package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.jeecgframework.web.system.service.SystemService;

import org.apache.log4j.Logger;
import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import yongxin.dao.inventory.InventoryMiniDao;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 库存有关
 * @author mly
 * @date 2015-12-20 21:18:59
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/inventoryMinidaoController")
public class InventoryMinidaoController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(InventoryMinidaoController.class);

	@Autowired
	private InventoryMiniDao inventoryMiniDao;
	@Autowired
	private SystemService systemService;
	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 辅助材料进耗存 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "fjhc")
	public ModelAndView fjhc(HttpServletRequest request)
	{
		return new ModelAndView("yongxin/kingdeeReport/fjhcList");
	}
    
	/**
	 * 原材料进耗存 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "yjhc")
	public ModelAndView yjhc(HttpServletRequest request)
	{
		return new ModelAndView("yongxin/kingdeeReport/yjhcList");
	}
	
	   
		/**
		 * 成品进耗存 页面跳转
		 * 
		 * @return
		 */
		@RequestMapping(params = "cpjhc")
		public ModelAndView cpjhc(HttpServletRequest request)
		{
			return new ModelAndView("yongxin/kingdeeReport/cpjhcList");
		}
	
	/**
	 * 库存月报表明细（其他增加）
	 * 
	 * @return
	 */
	@RequestMapping(params = "qtzj")
	public ModelAndView qtzj(HttpServletRequest request)
	{
		return new ModelAndView("yongxin/kingdeeReport/qtzjList");
	}
	/**
	 * 库存月报表明细（其他减少）
	 * 
	 * @return
	 */
	@RequestMapping(params = "qtjs")
	public ModelAndView qtjs(HttpServletRequest request)
	{
		return new ModelAndView("yongxin/kingdeeReport/qtjsList");
	}
	
	/**
	 * easyui AJAX请求数据   辅助材料进耗存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
     
		 String FDate = request.getParameter("FDate");
		//sql+="	-- 辅助材料进耗存                                                                                                                                                              ";
		String sql ="	DECLARE @RQ SMALLDATETIME                                                                                                                                                      ";
		sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                                ";
		sql+="	DECLARE @RQDAY2_M SMALLDATETIME                                                                                                                                                ";
		sql+="                                                                                                                                                                                 ";
		sql+="	DECLARE @YEAR INT                                                                                                                                                              ";
		sql+="	DECLARE @MONTH INT                                                                                                                                                             ";
		//sql+="	--日期参数                                                                                                                                                                     ";
		//sql+="	SET @RQ='2015-12-01'    --查询账期                                                                                                                                             ";
		sql+=" SET @RQ='" + FDate + "-01'";
		sql+="	SET @YEAR=YEAR(@RQ)                                                                                                                                                            ";
		sql+="	SET @MONTH=MONTH(@RQ)                                                                                                                                                          ";
		sql+="                                                                                                                                                                                 ";
		//sql+="	--本月第一天                                                                                                                                                                   ";
		sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                                                                                           ";
		//sql+="	--本月最后一天                                                                                                                                                                 ";
		sql+="	SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                                                                                      ";
		sql+="                                                                                                                                                                                 ";
		sql+="	Set NoCount On                                                                                                                                                                 ";
		sql+="	 Create Table #Happen(                                                                                                                                                         ";
		sql+="			FItemID int Null,                                                                                                                                                      ";
		sql+="			FStockID int Null,                                                                                                                                                     ";
		sql+="			FStockPlaceID int Null,                                                                                                                                                ";
		sql+="			FBatchNo Varchar(200),                                                                                                                                                 ";
		sql+="			FAuxPropID INT NOT NULL DEFAULT(0),                                                                                                                                    ";
		sql+="			FBegQty decimal(28,4),                                                                                                                                                 ";
		sql+="			FBegBal decimal(28,4),                                                                                                                                                 ";
		sql+="			FInQty  decimal(28,4),                                                                                                                                                 ";
		sql+="			FInPrice  decimal(28,4),                                                                                                                                               ";
		sql+="			FInAmount decimal(28,4),                                                                                                                                               ";
		sql+="			FOutQty decimal(28,4),                                                                                                                                                 ";
		sql+="			FOutPrice  decimal(28,4),                                                                                                                                              ";
		sql+="			FOutAmount decimal(28,4),                                                                                                                                              ";
		sql+="	FInSecQty decimal(28,4) Default(0),                                                                                                                                            ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                           ";
		sql+="	FBegSecQty decimal(28,4) Default(0)) Insert Into #Happen                                                                                                                       ";
		sql+="	 Select  v2.FItemID,v2.FStockID,Isnull(v2.FStockPlaceID,0),v2.FBatchNo,v2.FAuxPropID,                                                                                          ";
		sql+="		  Sum (v2.FBegQty), case when t1.FTrack = 81 Then Sum(Round(v2.FBegBal,2) - Round(v2.FBegDiff,2)) Else Sum(Round(v2.FBegBal,2)) End ,0,0,0,0,0,0,                          ";
		sql+="		  0,0,Sum(v2.FSecBegQty)                                                                                                                                                   ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICInvbal v2                                                                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1  On v2.FItemID=t1.FItemID                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FStockPlaceID=t11.FSPID                                                                                             ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Where v2.FYear=@YEAR And v2.FPeriod=@MONTH                                                                                                                                    ";
		sql+="	 And t1.FNumber>='F.BZ.001' And t1. FNumber<='F.ZB.PZJ.ZG.002' And t2.FNumber>='F.GL' AND t2.FNumber<='F.ZB'                                                                   ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v2.FItemID,v2.FStockID,v2.FStockPlaceID,v2.FBatchNo,v2.FAuxPropID,t1.FTrack                                                                                          ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                          ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                       ";
		sql+="	 Case When v1.FTranType In(1,2,5,10,40,100,101,102) And t1.FTrack<>81 Then Max(IsNull(v2.FPrice,0))                                                                            ";
		sql+="		  When v1.FTranType In(1,2,5,10,40,100,101,102,41) And t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0))                                                                      ";
		sql+="		  When v1.FTranType = 41 Then Max(IsNull(v2.FPriceRef,0)) Else 0 End,                                                                                                      ";
		sql+="	 Case When v1.FTranType In(1,2,5,10,40,100,101,102) And t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                 ";
		sql+="		  When v1.FTranType In(1,2,5,10,40,100,101,102,41) And t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0))                                                            ";
		sql+="		  When v1.FTranType =41 Then Sum(IsNull(Round(v2.FAmtRef,2),0)) Else 0 End ,                                                                                               ";
		sql+="	 0,0,0,Sum(IsNull(v2.FSecQty,0)),0,0                                                                                                                                           ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                  ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                           ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                   ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Where (v1.FTranType In (1,2,5,10,40,101,102,41) Or (V1.FTranType=100 And V1.FBillTypeID=12542)) And v1.FDate >=@RQDAY1_M                                                      ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                     ";
		sql+="	 And t1.FNumber>='F.BZ.001' And t1. FNumber<='F.ZB.PZJ.ZG.002' And t2.FNumber>='F.GL' AND t2.FNumber<='F.ZB'                                                                   ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                       ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                    ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                          ";
		sql+="	 0,0,0,                                                                                                                                                                        ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                       ";
		sql+="	 Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0))                                                                                                                        ";
		sql+="		  When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,                                                                                                          ";
		sql+="	 Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                              ";
		sql+="		  When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,                                                                                                ";
		sql+="	0,Sum(IsNull(v2.FSecQty,0)),0                                                                                                                                                  ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                  ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                           ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                   ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Where (v1.FTranType In (21,28,29,43) Or (V1.FTranType=100 And V1.FBillTypeID=12541)) And v1.FDate >=@RQDAY1_M                                                                 ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                     ";
		sql+="	 And t1.FNumber>='F.BZ.001' And t1. FNumber<='F.ZB.PZJ.ZG.002' And t2.FNumber>='F.GL' AND t2.FNumber<='F.ZB'                                                                   ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                       ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                    ";
		sql+="	Insert Into #Happen Select v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,                                      ";
		sql+="		0,0,0,0,0,                                                                                                                                                                 ";
		sql+="		Sum(IsNull(v2.FQty,0)),                                                                                                                                                    ";
		sql+="		Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0)) Else Max(IsNull(v2.FPlanPrice,0)) End,                                                                              ";
		sql+="		Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0)) Else Sum(IsNull(Round(v2.FPlanAmount,2),0)) End,                                                          ";
		sql+="		0,0,Sum(IsNull(v2.FSecQty,0))                                                                                                                                              ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                  ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                           ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FSCStockID=t2.FItemID                                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3  On t1.FStoreUnitID=t3.FMeasureUnitID                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On (Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End)=t11.FSPID                                              ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Where v1.FTranType In (24,41)                                                                                                                                                 ";
		sql+="	 And v1.FDate >=@RQDAY1_M                                                                                                                                                      ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                     ";
		sql+="	 And t1.FNumber>='F.BZ.001' And t1. FNumber<='F.ZB.PZJ.ZG.002' And t2.FNumber>='F.GL' AND t2.FNumber<='F.ZB'                                                                   ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                       ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                 ";
		sql+="	 Select v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID,                                                                                                     ";
		sql+="		   Sum(v1.FBegQty) As FBegQty,Sum(v1.FBegBal) As FBegBal,                                                                                                                  ";
		sql+="		   Sum(v1.FInQty) As FInQty,Max(v1.FInPrice) As FInPrice,Sum(v1.FInAmount) As FInAmount,                                                                                   ";
		sql+="		   Sum(v1.FOutQty) As FOutQty,Max(v1.FOutPrice) As FOutPrice,Sum(v1.FOutAmount) As FOutAmount,                                                                             ";
		sql+="		   Sum(v1.FInSecQty) As FInSecQty,Sum(v1.FOutSecQty) As FOutSecQty,Sum(v1.FBegSecQty) As FBegSecQty Into #Happen1 From #Happen v1                                          ";
		sql+="	 Where 1 = 1                                                                                                                                                                   ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID                                                                                                    ";
		sql+="	SET NOCOUNT ON                                                                                                                                                                 ";
		sql+="	CREATE TABLE #ItemLevel(                                                                                                                                                       ";
		sql+="	 FNumber2 Varchar(355),                                                                                                                                                        ";
		sql+="	 FName2 Varchar(355),                                                                                                                                                          ";
		sql+="	 FItemID int,                                                                                                                                                                  ";
		sql+="	 FNumber Varchar(355))                                                                                                                                                         ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 INSERT INTO #ItemLevel SELECT                                                                                                                                                 ";
		sql+="	 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE                                                                            ";
		sql+="	 SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1)  END,                                                                                         ";
		sql+="	 '',                                                                                                                                                                           ";
		sql+="	 FItemID,FNumber FROM RemoteServer.AIS2009.dbo.t_Item                                                                                                                          ";
		sql+="	 WHERE FItemClassID=4                                                                                                                                                          ";
		sql+="	 AND FDetail=1 AND FNumber>='F.BZ.001' AND FNumber<='F.ZB.PZJ.ZG.002'  And exists (Select FItemID From #Happen Where #Happen.FItemID=t_Item.FItemID)                           ";
		sql+="	 UPDATE t0 SET t0.FName2=t2.FName                                                                                                                                              ";
		sql+="	  FROM #ItemLevel t0 left join RemoteServer.AIS2009.dbo.t_Item t2 On t0.FNumber2=t2.FNumber  AND t2.FItemClassID=4 AND t2.FDetail=0                                            ";
		sql+="                                                                                                                                                                                 ";
		sql+="	Create Table #Data(                                                                                                                                                            ";
		sql+="	FName2 Varchar(355) Null,                                                                                                                                                      ";
		sql+="		 FNumber  Varchar(355) null,                                                                                                                                               ";
		sql+="		 FShortNumber  Varchar(355) null,                                                                                                                                          ";
		sql+="		 FName  Varchar(355) null,                                                                                                                                                 ";
		sql+="		 FModel  Varchar(355) null,                                                                                                                                                ";
		sql+="		 FUnitName  Varchar(355) null,                                                                                                                                             ";
		sql+="		 FQtyDecimal smallint null,                                                                                                                                                ";
		sql+="		 FPriceDecimal smallint null,                                                                                                                                              ";
		sql+="		 FBegQty decimal(28,4),                                                                                                                                                    ";
		sql+="		 FBegPrice decimal(28,4),                                                                                                                                                  ";
		sql+="		 FBegBal decimal(28,4),                                                                                                                                                    ";
		sql+="		 FInQty  decimal(28,4),                                                                                                                                                    ";
		sql+="		 FInPrice  decimal(28,4),                                                                                                                                                  ";
		sql+="		 FInAmount decimal(28,4),                                                                                                                                                  ";
		sql+="		 FOutQty decimal(28,4),                                                                                                                                                    ";
		sql+="		 FOutPrice decimal(28,4),                                                                                                                                                  ";
		sql+="		 FOutAmount decimal(28,4),                                                                                                                                                 ";
		sql+="		 FEndQty decimal(28,4),                                                                                                                                                    ";
		sql+="		 FEndPrice decimal(28,4),                                                                                                                                                  ";
		sql+="		 FEndAmount decimal(28,4),                                                                                                                                                 ";
		sql+="		 FSumSort smallint not null Default(0),                                                                                                                                    ";
		sql+="		 FID int IDENTITY,                                                                                                                                                         ";
		sql+="	FBegSecQty decimal(28,4) Default(0),FInSecQty decimal(28,4) Default(0),                                                                                                        ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                           ";
		sql+="	FBalSecQty decimal(28,4) Default(0)) Insert Into #Data                                                                                                                         ";
		sql+="	select FName2,                                                                                                                                                                 ";
		sql+="	FNumber,'','','','',6,4,sum(FBegQty),case when sum(FBegQty) <> 0 then sum(FBegBal)/sum(FBegQty) else 0 end,sum(FBegBal),sum(FInQty),                                           ";
		sql+="	case when sum(FInQty) <> 0 then sum(FInAmount)/ sum(FInQty) else 0 end,sum(FInAmount),sum(FOutQty)                                                                             ";
		sql+="	,case when sum(FOutQty) <> 0 then sum(FOutAmount)/sum(FOutQty) Else 0 end,sum(FOutAmount),sum(FEndQty),case when sum(FEndQty)<>0                                               ";
		sql+="	then sum(FEndAmount)/sum(FEndQty) else 0 end,sum(FEndAmount),                                                                                                                  ";
		sql+="	Case   When   Grouping(FName2)=1 THEN 107                                                                                                                                      ";
		sql+="	  When   Grouping(FNumber)=1 THEN 108  Else   0 END                                                                                                                            ";
		sql+="	,Sum (FBegSecQty), Sum(FInSecQty), Sum(FOutSecQty), Sum(FBalSecQty) FROM ( Select tt1.FName2 as FName2,t1.FNumber as FNumber,'' as col1,'' as col2,                            ";
		sql+="	'' as col3,'' as col4,6 as col5,4 as col6,                                                                                                                                     ";
		sql+="	SUM(ISNULL(v2.FBegQty,0)) as FBegQty,Case When SUM(ISNULL(v2.FBegQty,0))<>0 then SUM(ISNULL(FBegBal,0))/SUM(cast(ISNULL(FBegQty,0) as decimal(28,4))) Else 0 End as FBegPrice, ";
		sql+="	SUM(ISNULL(v2.FBegBal,0)) as FBegBal,SUM(ISNULL(FInQty,0)) as FInQty,Case When SUM(ISNULL(FInQty,0))<>0 Then SUM(ISNULL(FInAmount,0))/SUM(cast(FInQty as decimal(28,4)))       ";
		sql+="	Else 0 End as FInPrice,                                                                                                                                                        ";
		sql+="	SUM(ISNULL(FInAmount,0)) as FInAmount,SUM(ISNULL(FOutQty,0)) as FOutQty, Case When SUM(ISNULL(FOutQty,0))<>0                                                                   ";
		sql+="	Then SUM(ISNULL(FOutAmount,0))/SUM(cast(ISNULL(FOutQty,0) as decimal(28,4))) Else 0 End as FOutPrice,                                                                          ";
		sql+="	SUM(ISNULL(FOutAmount,0)) as FOutAmount,SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0)) as FEndQty,                                                        ";
		sql+="	Case When SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))<>0 Then                                                                                          ";
		sql+="	cast((SUM(ISNULL(FBegBal,0))+SUM(ISNULL(FInAmount,0))-SUM(ISNULL(FOutAmount,0))) as                                                                                            ";
		sql+="	decimal(28,4))/cast((SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))) as decimal(28,4)) Else 0 End as FEndPrice,                                           ";
		sql+="	Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0)) as FEndAmount,0 as FSumSort,Sum(ISNULL(v2.FBegSecQty,0)) as                                          ";
		sql+="	FBegSecQty,Sum(ISNULL(v2.FInSecQty,0)) as FInSecQty,Sum(ISNULL(v2.FOutSecQty,0)) as FOutSecQty,                                                                                ";
		sql+="	Sum(ISNULL(v2.FBegSecQty,0))+Sum(ISNULL(v2.FInSecQty,0))-Sum(ISNULL(v2.FOutSecQty,0)) as FBalSecQty                                                                            ";
		sql+="	 From #Happen1 v2                                                                                                                                                              ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_AuxItem ta On v2.FAuxPropID=ta.FItemID                                                                                                   ";
		sql+="	,#ItemLevel tt1                                                                                                                                                                ";
		sql+="	 Where 1=1                                                                                                                                                                     ";
		sql+="	 AND t1.FItemID=tt1.FItemID                                                                                                                                                    ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By tt1.FName2,t1.FNumber                                                                                                                                                ";
		sql+="	 ) t Group by FName2,                                                                                                                                                          ";
		sql+="	FNumber with rollup                                                                                                                                                            ";
		sql+="	 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel,                                                                                          ";
		sql+="	 t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal,                                                                                        ";
		sql+="	 t1.FInPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FInPrice End),                                                                                                 ";
		sql+="	 t1.FOutPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FOutPrice End)                                                                                                ";
		sql+="	 From #DATA t1 Left Join RemoteServer.AIS2009.dbo.t_ICItem t2 On t1.FNumber = t2.FNumber                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 On t2.FUnitID=t3.FMeasureUnitID                                                                                           ";
		sql+="	 Where t3.FStandard=1                                                                                                                                                          ";
		sql+="	update #data set FshortNumber = '合计' where fnumber = '合计'                                                                                                                  ";
		sql+="	Update #Data Set  FName2=FName2+'(小计)'  Where FSumSort=108                                                                                                                   ";
		sql+="	Update #Data Set FName2='合计' Where FSumSort=107                                                                                                                              ";
		sql+="	Update #Data Set FSumSort=101   Where FSumSort=107                                                                                                                             ";
		sql+="                                                                                                                                                                                 ";
		sql+="	Select td.*,tm.FName As FSecUnitName From #Data td                                                                                                                             ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t On t.FNumber=td.FNumber                                                                                                         ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit tm On t.FSecUnitID=tm.FMeasureUnitID                                                                                         ";
		sql+="	Where 1=1                                                                                                                                                                      ";
		sql+="	 And td.FSumSort>100                                                                                                                                                           ";
		sql+="	 Order by td.FID Drop Table #Data                                                                                                                                              ";
		sql+="	Drop Table #ItemLevel                                                                                                                                                          ";
		sql+="	 Drop Table #Happen                                                                                                                                                            ";
		sql+="	 Drop Table #Happen1                                                                                                                                                           ";
   

		List<Map> list = inventoryMiniDao.queryFJHC(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		TagUtil.datagrid(response, dataGrid);
	}
     
	
	
	/**
	 * easyui AJAX请求数据   原材料进耗存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "y_datagrid")
	public void y_datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
	     
		String FDate = request.getParameter("FDate");
		//sql+="	-- 原材料进耗存                                                                                                                                                                                                                                                 ";                                                                                                                                                                                                                                                                                         
		String sql ="	DECLARE @RQ SMALLDATETIME                                                                                                                                                                                                                                       ";
		sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                                                                                                                 ";
		sql+="	DECLARE @RQDAY2_M SMALLDATETIME                                                                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	DECLARE @YEAR INT                                                                                                                                                                                                                                               ";
		sql+="	DECLARE @MONTH INT                                                                                                                                                                                                                                              ";
		//sql+="	--日期参数                                                                                                                                                                                                                                                      ";
		//sql+="	SET @RQ='2016-01-01'    --查询账期                                                                                                                                                                                                                              ";
		sql+=" SET @RQ='" + FDate + "-01'";
		sql+="	SET @YEAR=YEAR(@RQ)                                                                                                                                                                                                                                             ";
		sql+="	SET @MONTH=MONTH(@RQ)                                                                                                                                                                                                                                           ";
		sql+="                                                                                                                                                                                                                                                                  ";
		//sql+="	--本月第一天                                                                                                                                                                                                                                                    ";
		sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                                                                                                                                                                            ";
		//sql+="	--本月最后一天                                                                                                                                                                                                                                                  ";
		sql+="	SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                                                                                                                                                                       ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	Set NoCount On                                                                                                                                                                                                                                                  ";
		sql+="	 Create Table #Happen(                                                                                                                                                                                                                                          ";
		sql+="			FItemID int Null,                                                                                                                                                                                                                                       ";
		sql+="			FStockID int Null,                                                                                                                                                                                                                                      ";
		sql+="			FStockPlaceID int Null,                                                                                                                                                                                                                                 ";
		sql+="			FBatchNo Varchar(200),                                                                                                                                                                                                                                  ";
		sql+="			FAuxPropID INT NOT NULL DEFAULT(0),                                                                                                                                                                                                                     ";
		sql+="			FBegQty decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="			FBegBal decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="			FInQty  decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="			FInPrice  decimal(28,4),                                                                                                                                                                                                                                ";
		sql+="			FInAmount decimal(28,4),                                                                                                                                                                                                                                ";
		sql+="			FOutQty decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="			FOutPrice  decimal(28,4),                                                                                                                                                                                                                               ";
		sql+="			FOutAmount decimal(28,4),                                                                                                                                                                                                                               ";
		sql+="	FInSecQty decimal(28,4) Default(0),                                                                                                                                                                                                                             ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                                                                                                            ";
		sql+="	FBegSecQty decimal(28,4) Default(0)) Insert Into #Happen                                                                                                                                                                                                        ";
		sql+="	 Select  v2.FItemID,v2.FStockID,Isnull(v2.FStockPlaceID,0),v2.FBatchNo,v2.FAuxPropID,                                                                                                                                                                           ";
		sql+="		  Sum (v2.FBegQty), case when t1.FTrack = 81 Then Sum(Round(v2.FBegBal,2) - Round(v2.FBegDiff,2)) Else Sum(Round(v2.FBegBal,2)) End ,0,0,0,0,0,0,                                                                                                           ";
		sql+="		  0,0,Sum(v2.FSecBegQty)                                                                                                                                                                                                                                    ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICInvbal v2                                                                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1  On v2.FItemID=t1.FItemID                                                                                                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FStockPlaceID=t11.FSPID                                                                                                                                                                              ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Where v2.FYear=@YEAR And v2.FPeriod=@MONTH                                                                                                                                                                                                                     ";
		sql+="	 And t1.FNumber>='Y.B.L.301.02' And t1. FNumber<='Y.ZT.C1921.01'                                                                                                                                                                                                ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v2.FItemID,v2.FStockID,v2.FStockPlaceID,v2.FBatchNo,v2.FAuxPropID,t1.FTrack                                                                                                                                                                           ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                                                                                                           ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                                                                        ";
		sql+="	 Case When v1.FTranType In(1,2,5,10,40,100,101,102) And t1.FTrack<>81 Then Max(IsNull(v2.FPrice,0))                                                                                                                                                             ";
		sql+="		  When v1.FTranType In(1,2,5,10,40,100,101,102,41) And t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0))                                                                                                                                                       ";
		sql+="		  When v1.FTranType = 41 Then Max(IsNull(v2.FPriceRef,0)) Else 0 End,                                                                                                                                                                                       ";
		sql+="	 Case When v1.FTranType In(1,2,5,10,40,100,101,102) And t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                                                                  ";
		sql+="		  When v1.FTranType In(1,2,5,10,40,100,101,102,41) And t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0))                                                                                                                                             ";
		sql+="		  When v1.FTranType =41 Then Sum(IsNull(Round(v2.FAmtRef,2),0)) Else 0 End ,                                                                                                                                                                                ";
		sql+="	 0,0,0,Sum(IsNull(v2.FSecQty,0)),0,0                                                                                                                                                                                                                            ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                                                                   ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Where (v1.FTranType In (1,2,5,10,40,101,102,41) Or (V1.FTranType=100 And V1.FBillTypeID=12542)) And v1.FDate >=@RQDAY1_M                                                                                                                                       ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                                                                                                      ";
		sql+="	 And t1.FNumber>='Y.B.L.301.02' And t1. FNumber<='Y.ZT.C1921.01'                                                                                                                                                                                                ";
		sql+="	 And v1.FCancelLation=0                                                                                                                                                                                                                                         ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                                                                     ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                                                                                                           ";
		sql+="	 0,0,0,                                                                                                                                                                                                                                                         ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                                                                        ";
		sql+="	 Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0))                                                                                                                                                                                                         ";
		sql+="		  When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,                                                                                                                                                                                           ";
		sql+="	 Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                                                                                                               ";
		sql+="		  When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,                                                                                                                                                                                 ";
		sql+="	0,Sum(IsNull(v2.FSecQty,0)),0                                                                                                                                                                                                                                   ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                                                                   ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Where (v1.FTranType In (21,28,29,43) Or (V1.FTranType=100 And V1.FBillTypeID=12541)) And v1.FDate >=@RQDAY1_M                                                                                                                                                  ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                                                                                                      ";
		sql+="	 And t1.FNumber>='Y.B.L.301.02' And t1. FNumber<='Y.ZT.C1921.01'                                                                                                                                                                                                ";
		sql+="	 And v1.FCancelLation=0                                                                                                                                                                                                                                         ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                                                                     ";
		sql+="	Insert Into #Happen Select v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,                                                                                                                       ";
		sql+="		0,0,0,0,0,                                                                                                                                                                                                                                                  ";
		sql+="		Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                                                                     ";
		sql+="		Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0)) Else Max(IsNull(v2.FPlanPrice,0)) End,                                                                                                                                                               ";
		sql+="		Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0)) Else Sum(IsNull(Round(v2.FPlanAmount,2),0)) End,                                                                                                                                           ";
		sql+="		0,0,Sum(IsNull(v2.FSecQty,0))                                                                                                                                                                                                                               ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                                                                   ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FSCStockID=t2.FItemID                                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3  On t1.FStoreUnitID=t3.FMeasureUnitID                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On (Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End)=t11.FSPID                                                                                                                               ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Where v1.FTranType In (24,41)                                                                                                                                                                                                                                  ";
		sql+="	 And v1.FDate >=@RQDAY1_M                                                                                                                                                                                                                                       ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                                                                                                      ";
		sql+="	 And t1.FNumber>='Y.B.L.301.02' And t1. FNumber<='Y.ZT.C1921.01'                                                                                                                                                                                                ";
		sql+="	 And v1.FCancelLation=0                                                                                                                                                                                                                                         ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                  ";
		sql+="	 Select v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID,                                                                                                                                                                                      ";
		sql+="		   Sum(v1.FBegQty) As FBegQty,Sum(v1.FBegBal) As FBegBal,                                                                                                                                                                                                   ";
		sql+="		   Sum(v1.FInQty) As FInQty,Max(v1.FInPrice) As FInPrice,Sum(v1.FInAmount) As FInAmount,                                                                                                                                                                    ";
		sql+="		   Sum(v1.FOutQty) As FOutQty,Max(v1.FOutPrice) As FOutPrice,Sum(v1.FOutAmount) As FOutAmount,                                                                                                                                                              ";
		sql+="		   Sum(v1.FInSecQty) As FInSecQty,Sum(v1.FOutSecQty) As FOutSecQty,Sum(v1.FBegSecQty) As FBegSecQty Into #Happen1 From #Happen v1                                                                                                                           ";
		sql+="	 Where 1 = 1                                                                                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID                                                                                                                                                                                     ";
		sql+="	SET NOCOUNT ON                                                                                                                                                                                                                                                  ";
		sql+="	CREATE TABLE #ItemLevel(                                                                                                                                                                                                                                        ";
		sql+="	 FNumber2 Varchar(355),                                                                                                                                                                                                                                         ";
		sql+="	 FName2 Varchar(355),                                                                                                                                                                                                                                           ";
		sql+="	 FItemID int,                                                                                                                                                                                                                                                   ";
		sql+="	 FNumber Varchar(355))                                                                                                                                                                                                                                          ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 INSERT INTO #ItemLevel SELECT                                                                                                                                                                                                                                  ";
		sql+="	 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1)  END,                                                                       ";
		sql+="	 '',                                                                                                                                                                                                                                                            ";
		sql+="	 FItemID,FNumber FROM RemoteServer.AIS2009.dbo.t_Item                                                                                                                                                                                                           ";
		sql+="	 WHERE FItemClassID=4                                                                                                                                                                                                                                           ";
		sql+="	 AND FDetail=1 AND FNumber>='Y.B.L.301.02' AND FNumber<='Y.ZT.C1921.01'  And exists (Select FItemID From #Happen Where #Happen.FItemID=t_Item.FItemID)                                                                                                          ";
		sql+="	 UPDATE t0 SET t0.FName2=t2.FName                                                                                                                                                                                                                               ";
		sql+="	  FROM #ItemLevel t0 left join RemoteServer.AIS2009.dbo.t_Item t2 On t0.FNumber2=t2.FNumber  AND t2.FItemClassID=4 AND t2.FDetail=0                                                                                                                             ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	Create Table #Data(                                                                                                                                                                                                                                             ";
		sql+="	FName2 Varchar(355) Null,                                                                                                                                                                                                                                       ";
		sql+="		 FNumber  Varchar(355) null,                                                                                                                                                                                                                                ";
		sql+="		 FShortNumber  Varchar(355) null,                                                                                                                                                                                                                           ";
		sql+="		 FName  Varchar(355) null,                                                                                                                                                                                                                                  ";
		sql+="		 FModel  Varchar(355) null,                                                                                                                                                                                                                                 ";
		sql+="		 FUnitName  Varchar(355) null,                                                                                                                                                                                                                              ";
		sql+="		 FQtyDecimal smallint null,                                                                                                                                                                                                                                 ";
		sql+="		 FPriceDecimal smallint null,                                                                                                                                                                                                                               ";
		sql+="		 FBegQty decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FBegPrice decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FBegBal decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FInQty  decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FInPrice  decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FInAmount decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FOutQty decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FOutPrice decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FOutAmount decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="		 FEndQty decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FEndPrice decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FEndAmount decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="		 FSumSort smallint not null Default(0),                                                                                                                                                                                                                     ";
		sql+="		 FID int IDENTITY,                                                                                                                                                                                                                                          ";
		sql+="	FBegSecQty decimal(28,4) Default(0),FInSecQty decimal(28,4) Default(0),                                                                                                                                                                                         ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                                                                                                            ";
		sql+="	FBalSecQty decimal(28,4) Default(0)) Insert Into #Data                                                                                                                                                                                                          ";
		sql+="	select FName2,                                                                                                                                                                                                                                                  ";
		sql+="	FNumber,'','','','',6,4,sum(FBegQty),case when sum(FBegQty) <> 0 then sum(FBegBal)/sum(FBegQty) else 0 end,sum(FBegBal),sum(FInQty),case when sum(FInQty) <> 0 then sum(FInAmount)/ sum(FInQty) else 0 end,sum(FInAmount),sum(FOutQty)                          ";
		sql+="	,case when sum(FOutQty) <> 0 then sum(FOutAmount)/sum(FOutQty) Else 0 end,sum(FOutAmount),sum(FEndQty),case when sum(FEndQty)<>0 then sum(FEndAmount)/sum(FEndQty) else 0 end,sum(FEndAmount),                                                                  ";
		sql+="	Case   When   Grouping(FName2)=1 THEN 107                                                                                                                                                                                                                       ";
		sql+="	  When   Grouping(FNumber)=1 THEN 108  Else   0 END                                                                                                                                                                                                             ";
		sql+="	,Sum (FBegSecQty), Sum(FInSecQty), Sum(FOutSecQty), Sum(FBalSecQty) FROM ( Select tt1.FName2 as FName2,t1.FNumber as FNumber,'' as col1,'' as col2,'' as col3,'' as col4,6 as col5,4 as col6,                                                                   ";
		sql+="	SUM(ISNULL(v2.FBegQty,0)) as FBegQty,Case When SUM(ISNULL(v2.FBegQty,0))<>0 then SUM(ISNULL(FBegBal,0))/SUM(cast(ISNULL(FBegQty,0) as decimal(28,4))) Else 0 End as FBegPrice,                                                                                  ";
		sql+="	SUM(ISNULL(v2.FBegBal,0)) as FBegBal,SUM(ISNULL(FInQty,0)) as FInQty,Case When SUM(ISNULL(FInQty,0))<>0 Then SUM(ISNULL(FInAmount,0))/SUM(cast(FInQty as decimal(28,4))) Else 0 End as FInPrice,                                                                ";
		sql+="	SUM(ISNULL(FInAmount,0)) as FInAmount,SUM(ISNULL(FOutQty,0)) as FOutQty, Case When SUM(ISNULL(FOutQty,0))<>0 Then SUM(ISNULL(FOutAmount,0))/SUM(cast(ISNULL(FOutQty,0) as decimal(28,4))) Else 0 End as FOutPrice,                                              ";
		sql+="	SUM(ISNULL(FOutAmount,0)) as FOutAmount,SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0)) as FEndQty,                                                                                                                                         ";
		sql+="	Case When SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))<>0 Then cast((SUM(ISNULL(FBegBal,0))+SUM(ISNULL(FInAmount,0))-SUM(ISNULL(FOutAmount,0))) as decimal(28,4))/cast((SUM(ISNULL(FBegQty,0))+                                          ";
		sql+="	SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))) as decimal(28,4)) Else 0 End as FEndPrice,                                                                                                                                                                        ";
		sql+="	Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0)) as FEndAmount,0 as FSumSort,Sum(ISNULL(v2.FBegSecQty,0)) as FBegSecQty,Sum(ISNULL(v2.FInSecQty,0)) as FInSecQty,Sum(ISNULL(v2.FOutSecQty,0)) as FOutSecQty,                           ";
		sql+="	Sum(ISNULL(v2.FBegSecQty,0))+Sum(ISNULL(v2.FInSecQty,0))-Sum(ISNULL(v2.FOutSecQty,0)) as FBalSecQty                                                                                                                                                             ";
		sql+="	 From #Happen1 v2                                                                                                                                                                                                                                               ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_AuxItem ta On v2.FAuxPropID=ta.FItemID                                                                                                                                                                                    ";
		sql+="	,#ItemLevel tt1                                                                                                                                                                                                                                                 ";
		sql+="	 Where 1=1                                                                                                                                                                                                                                                      ";
		sql+="	 AND t1.FItemID=tt1.FItemID                                                                                                                                                                                                                                     ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By tt1.FName2,t1.FNumber                                                                                                                                                                                                                                 ";
		sql+="	 Having NOT (SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))=0 AND Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0))=0)                                                                                             ";
		sql+="	 ) t Group by FName2,                                                                                                                                                                                                                                           ";
		sql+="	FNumber with rollup                                                                                                                                                                                                                                             ";
		sql+="	 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel,                                                                                                                                                                           ";
		sql+="	 t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal,                                                                                                                                                                         ";
		sql+="	 t1.FInPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FInPrice End),                                                                                                                                                                                  ";
		sql+="	 t1.FOutPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FOutPrice End)                                                                                                                                                                                 ";
		sql+="	 From #DATA t1 Left Join RemoteServer.AIS2009.dbo.t_ICItem t2 On t1.FNumber = t2.FNumber                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 On t2.FUnitID=t3.FMeasureUnitID                                                                                                                                                                            ";
		sql+="	 Where t3.FStandard=1                                                                                                                                                                                                                                           ";
		sql+="	update #data set FshortNumber = '合计' where fnumber = '合计'                                                                                                                                                                                                   ";
		sql+="	Update #Data Set  FName2=FName2+'(小计)'  Where FSumSort=108                                                                                                                                                                                                    ";
		sql+="	Update #Data Set FName2='合计' Where FSumSort=107                                                                                                                                                                                                               ";
		sql+="	Update #Data Set FSumSort=101   Where FSumSort=107                                                                                                                                                                                                              ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	Select td.*,tm.FName As FSecUnitName From #Data td                                                                                                                                                                                                              ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t On t.FNumber=td.FNumber                                                                                                                                                                                          ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit tm On t.FSecUnitID=tm.FMeasureUnitID                                                                                                                                                                          ";
		sql+="	Where 1=1                                                                                                                                                                                                                                                       ";
		sql+="	 And td.FSumSort>100                                                                                                                                                                                                                                            ";
		sql+="	 Order by td.FID Drop Table #Data                                                                                                                                                                                                                               ";
		sql+="	Drop Table #ItemLevel                                                                                                                                                                                                                                           ";
		sql+="	 Drop Table #Happen                                                                                                                                                                                                                                             ";
		sql+="	 Drop Table #Happen1                                                                                                                                                                                                                                            ";
		                                                                                                                                                                                                                                                                       
		
		
		List<Map> list = inventoryMiniDao.queryYJHC(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	
	/**
	 * easyui AJAX请求数据   成品进耗存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "cp_datagrid")
	public void cp_datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
		
		String FDate = request.getParameter("FDate");
		//sql+="	-- 成品进耗存 new                                                                                                                                                                                                   ";
		String sql ="	DECLARE @RQ SMALLDATETIME                                                                                                                                                                                           ";
		sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                                                                     ";
		sql+="	DECLARE @RQDAY2_M SMALLDATETIME                                                                                                                                                                                     ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	DECLARE @YEAR INT                                                                                                                                                                                                   ";
		sql+="	DECLARE @MONTH INT                                                                                                                                                                                                  ";
		//sql+="	--日期参数                                                                                                                                                                                                          ";
		//sql+="	SET @RQ='2014-10-01'    --查询账期                                                                                                                                                                                  ";
		sql+=" SET @RQ='" + FDate + "-01'";
		sql+="	SET @YEAR=YEAR(@RQ)                                                                                                                                                                                                 ";
		sql+="	SET @MONTH=MONTH(@RQ)                                                                                                                                                                                               ";
		sql+="                                                                                                                                                                                                                      ";
		//sql+="	--本月第一天                                                                                                                                                                                                        ";
		sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                                                                                                                                ";
		//sql+="	--本月最后一天                                                                                                                                                                                                      ";
		sql+="	SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                                                                                                                           ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	Set NoCount On                                                                                                                                                                                                      ";
		sql+="	 Create Table #Happen(                                                                                                                                                                                              ";
		sql+="			FItemID int Null,                                                                                                                                                                                           ";
		sql+="			FStockID int Null,                                                                                                                                                                                          ";
		sql+="			FStockPlaceID int Null,                                                                                                                                                                                     ";
		sql+="			FBatchNo Varchar(200),                                                                                                                                                                                      ";
		sql+="			FAuxPropID INT NOT NULL DEFAULT(0),                                                                                                                                                                         ";
		sql+="			FBegQty decimal(28,4),                                                                                                                                                                                      ";
		sql+="			FBegBal decimal(28,4),                                                                                                                                                                                      ";
		sql+="			FInQty  decimal(28,4),                                                                                                                                                                                      ";
		sql+="			FInPrice  decimal(28,4),                                                                                                                                                                                    ";
		sql+="			FInAmount decimal(28,4),                                                                                                                                                                                    ";
		sql+="			FOutQty decimal(28,4),                                                                                                                                                                                      ";
		sql+="			FOutPrice  decimal(28,4),                                                                                                                                                                                   ";
		sql+="			FOutAmount decimal(28,4),                                                                                                                                                                                   ";
		sql+="	FInSecQty decimal(28,4) Default(0),                                                                                                                                                                                 ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                                                                ";
		sql+="	FBegSecQty decimal(28,4) Default(0)) Insert Into #Happen                                                                                                                                                            ";
		sql+="	 Select  v2.FItemID,v2.FStockID,Isnull(v2.FStockPlaceID,0),v2.FBatchNo,v2.FAuxPropID,                                                                                                                               ";
		sql+="		  Sum (v2.FBegQty), case when t1.FTrack = 81 Then Sum(Round(v2.FBegBal,2) - Round(v2.FBegDiff,2)) Else Sum(Round(v2.FBegBal,2)) End ,0,0,0,0,0,0,                                                               ";
		sql+="		  0,0,Sum(v2.FSecBegQty)                                                                                                                                                                                        ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICInvbal v2                                                                                                                                                                          ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1  On v2.FItemID=t1.FItemID                                                                                                                                           ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FStockPlaceID=t11.FSPID                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Where v2.FYear=@YEAR And v2.FPeriod=@MONTH                                                                                                                                                                         ";
		sql+="	 And t1.FNumber>='C.B.301.001' And t1. FNumber<='C.T.LZ.STW22.Y.300' And t2.FNumber>='C.BXG' AND t2.FNumber<='C.LZ'                                                                                                 ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Group By v2.FItemID,v2.FStockID,v2.FStockPlaceID,v2.FBatchNo,v2.FAuxPropID,t1.FTrack                                                                                                                               ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                                                               ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                            ";
		sql+="	 Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0))                                                                                                                                                             ";
		sql+="		  When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,                                                                                                                                               ";
		sql+="	 Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                                                                   ";
		sql+="		  When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,                                                                                                                                     ";
		sql+="	 0,0,0,Sum(IsNull(v2.FSecQty,0)),0,0                                                                                                                                                                                ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                       ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                                                          ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                                                        ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Where v1.FDate >=@RQDAY1_M                                                                                                                                                                                         ";
		sql+="	 And v1.FDate <@RQDAY2_M + 1                                                                                                                                                                                        ";
		sql+="	 And (v1.FTranType In (1,2,5,10,40,101,102) Or (v1.FTranType=100 And v1.FBillTypeID=12542))                                                                                                                         ";
		sql+="	 And t1.FNumber>='C.B.301.001' And t1. FNumber<='C.T.LZ.STW22.Y.300' And t2.FNumber>='C.BXG' AND t2.FNumber<='C.LZ'                                                                                                 ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                                                            ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                         ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                                                               ";
		sql+="	 0,0,0,                                                                                                                                                                                                             ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                            ";
		sql+="	 Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0))                                                                                                                                                             ";
		sql+="		  When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,                                                                                                                                               ";
		sql+="	 Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                                                                   ";
		sql+="		  When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,                                                                                                                                     ";
		sql+="		  0,Sum(IsNull(v2.FSecQty,0)),0                                                                                                                                                                                 ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                       ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                                                          ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FSCSPID=t11.FSPID                                                                                                                                        ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Where v1.FDate >@RQDAY1_M                                                                                                                                                                                          ";
		sql+="	 And v1.FDate < @RQDAY2_M + 1                                                                                                                                                                                       ";
		sql+="	 And (v1.FTranType In (21,28,29,43) Or (v1.FTranType=100 And v1.FBillTypeID=12541))                                                                                                                                 ";
		sql+="	 And t1.FNumber>='C.B.301.001' And t1. FNumber<='C.T.LZ.STW22.Y.300' And t2.FNumber>='C.BXG' AND t2.FNumber<='C.LZ'                                                                                                 ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                                                            ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                         ";
		sql+="	Insert Into #Happen Select v2.FItemID,t2.FItemID,IsNull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,                                                                                                                    ";
		sql+="		0,0,0,0,0,                                                                                                                                                                                                      ";
		sql+="		Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                         ";
		sql+="		Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0)) Else Max(IsNull(v2.FPlanPrice,0)) End,                                                                                                                   ";
		sql+="		Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0)) Else Sum(IsNull(Round(v2.FPlanAmount,2),0)) End,                                                                                               ";
		sql+="		0,0,Sum(IsNull(v2.FSecQty,0))                                                                                                                                                                                   ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                       ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FSCStockID=t2.FItemID                                                                                                                                          ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3  On t1.FStoreUnitID=t3.FMeasureUnitID                                                                                                                          ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On  v2.FSCSPID=t11.FSPID                                                                                                                                       ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Where v1.FTranType In (24)                                                                                                                                                                                         ";
		sql+="	 And t1.FNumber>='C.B.301.001' And t1. FNumber<='C.T.LZ.STW22.Y.300' And t2.FNumber>='C.BXG' AND t2.FNumber<='C.LZ'                                                                                                 ";
		sql+="	 And v1.FDate >=@RQDAY1_M                                                                                                                                                                                           ";
		sql+="	 And v1.FDate <@RQDAY2_M + 1                                                                                                                                                                                        ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                                                            ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Group By v2.FItemID,t2.FItemID, v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                        ";
		sql+="	 Select v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID,                                                                                                                                          ";
		sql+="		   Sum(v1.FBegQty)/1000 As FBegQty,Sum(v1.FBegBal) As FBegBal,                                                                                                                                                  ";
		sql+="		   Sum(v1.FInQty)/1000 As FInQty,Max(v1.FInPrice) As FInPrice,Sum(v1.FInAmount) As FInAmount,                                                                                                                   ";
		sql+="		   Sum(v1.FOutQty)/1000 As FOutQty,Max(v1.FOutPrice) As FOutPrice,Sum(v1.FOutAmount) As FOutAmount,                                                                                                             ";
		sql+="		   Sum(v1.FInSecQty)/1000 As FInSecQty,Sum(v1.FOutSecQty)/1000 As FOutSecQty,Sum(v1.FBegSecQty)/1000 As FBegSecQty Into #Happen1 From #Happen v1                                                                ";
		sql+="	 Where 1 = 1                                                                                                                                                                                                        ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Group By v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID                                                                                                                                         ";
		sql+="	SET NOCOUNT ON                                                                                                                                                                                                      ";
		sql+="	CREATE TABLE #ItemLevel(                                                                                                                                                                                            ";
		sql+="	 FNumber1 Varchar(355),                                                                                                                                                                                             ";
		sql+="	 FName1 Varchar(355),                                                                                                                                                                                               ";
		sql+="	 FNumber2 Varchar(355),                                                                                                                                                                                             ";
		sql+="	 FName2 Varchar(355),                                                                                                                                                                                               ";
		sql+="	 FItemID int,                                                                                                                                                                                                       ";
		sql+="	 FNumber Varchar(355))                                                                                                                                                                                              ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 INSERT INTO #ItemLevel SELECT                                                                                                                                                                                      ";
		sql+="	 CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1)  END,                                                                                     ";
		sql+="	 '',                                                                                                                                                                                                                ";
		sql+="	 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,                                                                 ";
		sql+="	 CHARINDEX('.',FFullNumber)+1)-1)  END,                                                                                                                                                                             ";
		sql+="	 '',                                                                                                                                                                                                                ";
		sql+="	 FItemID,FNumber FROM RemoteServer.AIS2009.dbo.t_Item                                                                                                                                                               ";
		sql+="	 WHERE FItemClassID=4                                                                                                                                                                                               ";
		sql+="	 AND FDetail=1 AND FNumber>='C.B.301.001' AND FNumber<='C.T.LZ.STW22.Y.300'  And exists (Select FItemID From #Happen Where                                                                                          ";
		sql+="	 #Happen.FItemID=t_Item.FItemID)                                                                                                                                                                                    ";
		sql+="	 UPDATE t0 SET t0.FName1=t1.FName,t0.FName2=t2.FName                                                                                                                                                                ";
		sql+="	  FROM #ItemLevel t0 left join RemoteServer.AIS2009.dbo.t_Item t1 On t0.FNumber1=t1.FNumber  AND t1.FItemClassID=4 AND t1.FDetail=0                                                                                 ";
		sql+="	 left join RemoteServer.AIS2009.dbo.t_Item t2 On t0.FNumber2=t2.FNumber  AND t2.FItemClassID=4 AND t2.FDetail=0                                                                                                     ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	Create Table #Data(                                                                                                                                                                                                 ";
		sql+="	FName1 Varchar(355) Null,                                                                                                                                                                                           ";
		sql+="	FName2 Varchar(355) Null,                                                                                                                                                                                           ";
		sql+="		 FNumber  Varchar(355) null,                                                                                                                                                                                    ";
		sql+="		 FShortNumber  Varchar(355) null,                                                                                                                                                                               ";
		sql+="		 FName  Varchar(355) null,                                                                                                                                                                                      ";
		sql+="		 FModel  Varchar(355) null,                                                                                                                                                                                     ";
		sql+="		 FUnitName  Varchar(355) null,                                                                                                                                                                                  ";
		sql+="		 FQtyDecimal smallint null,                                                                                                                                                                                     ";
		sql+="		 FPriceDecimal smallint null,                                                                                                                                                                                   ";
		sql+="		 FBegQty decimal(28,4),                                                                                                                                                                                         ";
		sql+="		 FBegPrice decimal(28,4),                                                                                                                                                                                       ";
		sql+="		 FBegBal decimal(28,4),                                                                                                                                                                                         ";
		sql+="		 FInQty  decimal(28,4),                                                                                                                                                                                         ";
		sql+="		 FInPrice  decimal(28,4),                                                                                                                                                                                       ";
		sql+="		 FInAmount decimal(28,4),                                                                                                                                                                                       ";
		sql+="		 FOutQty decimal(28,4),                                                                                                                                                                                         ";
		sql+="		 FOutPrice decimal(28,4),                                                                                                                                                                                       ";
		sql+="		 FOutAmount decimal(28,4),                                                                                                                                                                                      ";
		sql+="		 FEndQty decimal(28,4),                                                                                                                                                                                         ";
		sql+="		 FEndPrice decimal(28,4),                                                                                                                                                                                       ";
		sql+="		 FEndAmount decimal(28,4),                                                                                                                                                                                      ";
		sql+="		 FSumSort smallint not null Default(0),                                                                                                                                                                         ";
		sql+="		 FID int IDENTITY,                                                                                                                                                                                              ";
		sql+="	FBegSecQty decimal(28,4) Default(0),FInSecQty decimal(28,4) Default(0),                                                                                                                                             ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                                                                ";
		sql+="	FBalSecQty decimal(28,4) Default(0)) Insert Into #Data                                                                                                                                                              ";
		sql+="	select FName1,                                                                                                                                                                                                      ";
		sql+="	FName2,                                                                                                                                                                                                             ";
		sql+="	FNumber,'','','','',6,4,sum(FBegQty),case when sum(FBegQty) <> 0 then sum(FBegBal)/sum(FBegQty) else 0 end,sum(FBegBal),sum(FInQty),                                                                                ";
		sql+="	case when sum(FInQty) <> 0 then sum(FInAmount)/ sum(FInQty) else 0 end,sum(FInAmount),sum(FOutQty)                                                                                                                  ";
		sql+="	,case when sum(FOutQty) <> 0 then sum(FOutAmount)/sum(FOutQty) Else 0 end,sum(FOutAmount),sum(FEndQty),case when sum(FEndQty)<>0                                                                                    ";
		sql+="	then sum(FEndAmount)/sum(FEndQty) else 0 end,sum(FEndAmount),                                                                                                                                                       ";
		sql+="	Case   When   Grouping(FName1)=1 THEN 106                                                                                                                                                                           ";
		sql+="	  When   Grouping(FName2)=1 THEN 107                                                                                                                                                                                ";
		sql+="	  When   Grouping(FNumber)=1 THEN 108  Else   0 END                                                                                                                                                                 ";
		sql+="	,Sum (FBegSecQty), Sum(FInSecQty), Sum(FOutSecQty), Sum(FBalSecQty) FROM ( Select tt1.FName1 as FName1,tt1.FName2 as FName2,t1.FNumber as FNumber,                                                                  ";
		sql+="	'' as col1,'' as col2,'' as col3,'' as col4,6 as col5,4 as col6,                                                                                                                                                    ";
		sql+="	SUM(ISNULL(v2.FBegQty,0)) as FBegQty,Case When SUM(ISNULL(v2.FBegQty,0))<>0 then SUM(ISNULL(FBegBal,0))/SUM(cast(ISNULL(FBegQty,0) as decimal(28,4)))                                                               ";
		sql+="	 Else 0 End as FBegPrice,                                                                                                                                                                                           ";
		sql+="	SUM(ISNULL(v2.FBegBal,0)) as FBegBal,SUM(ISNULL(FInQty,0)) as FInQty,Case When SUM(ISNULL(FInQty,0))<>0 Then SUM(ISNULL(FInAmount,0))/SUM(cast(FInQty as decimal(28,4)))                                            ";
		sql+="	 Else 0 End as FInPrice,                                                                                                                                                                                            ";
		sql+="	SUM(ISNULL(FInAmount,0)) as FInAmount,SUM(ISNULL(FOutQty,0)) as FOutQty, Case When SUM(ISNULL(FOutQty,0))<>0 Then SUM(ISNULL(FOutAmount,0))/SUM(cast(ISNULL(FOutQty,0) as decimal(28,4))) Else 0 End as FOutPrice,  ";
		sql+="	SUM(ISNULL(FOutAmount,0)) as FOutAmount,SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0)) as FEndQty,                                                                                             ";
		sql+="	Case When SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))<>0 Then cast((SUM(ISNULL(FBegBal,0))+SUM(ISNULL(FInAmount,0))-SUM(ISNULL(FOutAmount,0))) as                                           ";
		sql+="	decimal(28,4))/cast((SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))) as decimal(28,4)) Else 0 End as FEndPrice,                                                                                ";
		sql+="	Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0)) as FEndAmount,0 as FSumSort,Sum(ISNULL(v2.FBegSecQty,0)) as FBegSecQty,Sum(ISNULL(v2.FInSecQty,0)) as FInSecQty,                          ";
		sql+="	Sum(ISNULL(v2.FOutSecQty,0)) as FOutSecQty,Sum(ISNULL(v2.FBegSecQty,0))+Sum(ISNULL(v2.FInSecQty,0))-Sum(ISNULL(v2.FOutSecQty,0)) as FBalSecQty                                                                      ";
		sql+="	 From #Happen1 v2                                                                                                                                                                                                   ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                           ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_AuxItem ta On v2.FAuxPropID=ta.FItemID                                                                                                                                        ";
		sql+="	,#ItemLevel tt1                                                                                                                                                                                                     ";
		sql+="	 Where 1=1                                                                                                                                                                                                          ";
		sql+="	 AND t1.FItemID=tt1.FItemID                                                                                                                                                                                         ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Group By tt1.FName1,tt1.FName2,t1.FNumber                                                                                                                                                                          ";
		sql+="	 ) t Group by FName1,                                                                                                                                                                                               ";
		sql+="	FName2,                                                                                                                                                                                                             ";
		sql+="	FNumber with rollup                                                                                                                                                                                                 ";
		sql+="	 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel,                                                                                                                               ";
		sql+="	 t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal,                                                                                                                             ";
		sql+="	 t1.FInPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FInPrice End),                                                                                                                                      ";
		sql+="	 t1.FOutPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FOutPrice End)                                                                                                                                     ";
		sql+="	 From #DATA t1 Left Join RemoteServer.AIS2009.dbo.t_ICItem t2 On t1.FNumber = t2.FNumber                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 On t2.FUnitID=t3.FMeasureUnitID                                                                                                                                ";
		sql+="	 Where t3.FStandard=1                                                                                                                                                                                               ";
		sql+="	update #data set FshortNumber = '合计' where fnumber = '合计'                                                                                                                                                       ";
		sql+="	Update #Data Set  FName1=FName1+'(小计)'  Where FSumSort=107                                                                                                                                                        ";
		sql+="	Update #Data Set  FName2=FName2+'(小计)'  Where FSumSort=108                                                                                                                                                        ";
		sql+="	Update #Data Set FName1='合计' Where FSumSort=106                                                                                                                                                                   ";
		sql+="	Update #Data Set FSumSort=101   Where FSumSort=106                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	Select td.*,isnull(td.FName2,'') FName3,isnull(td.FNumber,'')  FNumber3,tm.FName As FSecUnitName From #Data td                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t On t.FNumber=td.FNumber                                                                                                                                              ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit tm On t.FSecUnitID=tm.FMeasureUnitID                                                                                                                              ";
		sql+="	 Where 1=1                                                                                                                                                                                                          ";
		sql+="	 Order by td.FID                                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                                                      ";
		sql+="	 Drop Table #Data                                                                                                                                                                                                   ";
		sql+="	Drop Table #ItemLevel                                                                                                                                                                                               ";
		sql+="	 Drop Table #Happen                                                                                                                                                                                                 ";
		sql+="	 Drop Table #Happen1                                                                                                                                                                                                ";

		List<Map> list = inventoryMiniDao.queryCPJHC(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		TagUtil.datagrid(response, dataGrid);
		
		
	}
	
	

	/**
	 * 
	 * 導出    成品进耗存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_cpjhc")
	public void export_cpjhc(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		
		String FDate = request.getParameter("FDate");
		//sql+="	-- 成品进耗存 new                                                                                                                                                                                                   ";
				String sql ="	DECLARE @RQ SMALLDATETIME                                                                                                                                                                                           ";
				sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                                                                     ";
				sql+="	DECLARE @RQDAY2_M SMALLDATETIME                                                                                                                                                                                     ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	DECLARE @YEAR INT                                                                                                                                                                                                   ";
				sql+="	DECLARE @MONTH INT                                                                                                                                                                                                  ";
				//sql+="	--日期参数                                                                                                                                                                                                          ";
				//sql+="	SET @RQ='2014-10-01'    --查询账期                                                                                                                                                                                  ";
				sql+=" SET @RQ='" + FDate + "-01'";
				sql+="	SET @YEAR=YEAR(@RQ)                                                                                                                                                                                                 ";
				sql+="	SET @MONTH=MONTH(@RQ)                                                                                                                                                                                               ";
				sql+="                                                                                                                                                                                                                      ";
				//sql+="	--本月第一天                                                                                                                                                                                                        ";
				sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                                                                                                                                ";
				//sql+="	--本月最后一天                                                                                                                                                                                                      ";
				sql+="	SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                                                                                                                           ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	Set NoCount On                                                                                                                                                                                                      ";
				sql+="	 Create Table #Happen(                                                                                                                                                                                              ";
				sql+="			FItemID int Null,                                                                                                                                                                                           ";
				sql+="			FStockID int Null,                                                                                                                                                                                          ";
				sql+="			FStockPlaceID int Null,                                                                                                                                                                                     ";
				sql+="			FBatchNo Varchar(200),                                                                                                                                                                                      ";
				sql+="			FAuxPropID INT NOT NULL DEFAULT(0),                                                                                                                                                                         ";
				sql+="			FBegQty decimal(28,4),                                                                                                                                                                                      ";
				sql+="			FBegBal decimal(28,4),                                                                                                                                                                                      ";
				sql+="			FInQty  decimal(28,4),                                                                                                                                                                                      ";
				sql+="			FInPrice  decimal(28,4),                                                                                                                                                                                    ";
				sql+="			FInAmount decimal(28,4),                                                                                                                                                                                    ";
				sql+="			FOutQty decimal(28,4),                                                                                                                                                                                      ";
				sql+="			FOutPrice  decimal(28,4),                                                                                                                                                                                   ";
				sql+="			FOutAmount decimal(28,4),                                                                                                                                                                                   ";
				sql+="	FInSecQty decimal(28,4) Default(0),                                                                                                                                                                                 ";
				sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                                                                ";
				sql+="	FBegSecQty decimal(28,4) Default(0)) Insert Into #Happen                                                                                                                                                            ";
				sql+="	 Select  v2.FItemID,v2.FStockID,Isnull(v2.FStockPlaceID,0),v2.FBatchNo,v2.FAuxPropID,                                                                                                                               ";
				sql+="		  Sum (v2.FBegQty), case when t1.FTrack = 81 Then Sum(Round(v2.FBegBal,2) - Round(v2.FBegDiff,2)) Else Sum(Round(v2.FBegBal,2)) End ,0,0,0,0,0,0,                                                               ";
				sql+="		  0,0,Sum(v2.FSecBegQty)                                                                                                                                                                                        ";
				sql+="	 From RemoteServer.AIS2009.dbo.ICInvbal v2                                                                                                                                                                          ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1  On v2.FItemID=t1.FItemID                                                                                                                                           ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                                                            ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FStockPlaceID=t11.FSPID                                                                                                                                  ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Where v2.FYear=@YEAR And v2.FPeriod=@MONTH                                                                                                                                                                         ";
				sql+="	 And t1.FNumber>='C.B.301.001' And t1. FNumber<='C.T.LZ.STW22.Y.300' And t2.FNumber>='C.BXG' AND t2.FNumber<='C.LZ'                                                                                                 ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Group By v2.FItemID,v2.FStockID,v2.FStockPlaceID,v2.FBatchNo,v2.FAuxPropID,t1.FTrack                                                                                                                               ";
				sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                                                               ";
				sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                            ";
				sql+="	 Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0))                                                                                                                                                             ";
				sql+="		  When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,                                                                                                                                               ";
				sql+="	 Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                                                                   ";
				sql+="		  When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,                                                                                                                                     ";
				sql+="	 0,0,0,Sum(IsNull(v2.FSecQty,0)),0,0                                                                                                                                                                                ";
				sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                       ";
				sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                            ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                                                          ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                                                        ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Where v1.FDate >=@RQDAY1_M                                                                                                                                                                                         ";
				sql+="	 And v1.FDate <@RQDAY2_M + 1                                                                                                                                                                                        ";
				sql+="	 And (v1.FTranType In (1,2,5,10,40,101,102) Or (v1.FTranType=100 And v1.FBillTypeID=12542))                                                                                                                         ";
				sql+="	 And t1.FNumber>='C.B.301.001' And t1. FNumber<='C.T.LZ.STW22.Y.300' And t2.FNumber>='C.BXG' AND t2.FNumber<='C.LZ'                                                                                                 ";
				sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                                                            ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                         ";
				sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                                                               ";
				sql+="	 0,0,0,                                                                                                                                                                                                             ";
				sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                            ";
				sql+="	 Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0))                                                                                                                                                             ";
				sql+="		  When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,                                                                                                                                               ";
				sql+="	 Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                                                                   ";
				sql+="		  When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,                                                                                                                                     ";
				sql+="		  0,Sum(IsNull(v2.FSecQty,0)),0                                                                                                                                                                                 ";
				sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                       ";
				sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                            ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                                                          ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FSCSPID=t11.FSPID                                                                                                                                        ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Where v1.FDate >@RQDAY1_M                                                                                                                                                                                          ";
				sql+="	 And v1.FDate < @RQDAY2_M + 1                                                                                                                                                                                       ";
				sql+="	 And (v1.FTranType In (21,28,29,43) Or (v1.FTranType=100 And v1.FBillTypeID=12541))                                                                                                                                 ";
				sql+="	 And t1.FNumber>='C.B.301.001' And t1. FNumber<='C.T.LZ.STW22.Y.300' And t2.FNumber>='C.BXG' AND t2.FNumber<='C.LZ'                                                                                                 ";
				sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                                                            ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                         ";
				sql+="	Insert Into #Happen Select v2.FItemID,t2.FItemID,IsNull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,                                                                                                                    ";
				sql+="		0,0,0,0,0,                                                                                                                                                                                                      ";
				sql+="		Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                         ";
				sql+="		Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0)) Else Max(IsNull(v2.FPlanPrice,0)) End,                                                                                                                   ";
				sql+="		Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0)) Else Sum(IsNull(Round(v2.FPlanAmount,2),0)) End,                                                                                               ";
				sql+="		0,0,Sum(IsNull(v2.FSecQty,0))                                                                                                                                                                                   ";
				sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                       ";
				sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                            ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FSCStockID=t2.FItemID                                                                                                                                          ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3  On t1.FStoreUnitID=t3.FMeasureUnitID                                                                                                                          ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On  v2.FSCSPID=t11.FSPID                                                                                                                                       ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Where v1.FTranType In (24)                                                                                                                                                                                         ";
				sql+="	 And t1.FNumber>='C.B.301.001' And t1. FNumber<='C.T.LZ.STW22.Y.300' And t2.FNumber>='C.BXG' AND t2.FNumber<='C.LZ'                                                                                                 ";
				sql+="	 And v1.FDate >=@RQDAY1_M                                                                                                                                                                                           ";
				sql+="	 And v1.FDate <@RQDAY2_M + 1                                                                                                                                                                                        ";
				sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                                                            ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Group By v2.FItemID,t2.FItemID, v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                        ";
				sql+="	 Select v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID,                                                                                                                                          ";
				sql+="		   Sum(v1.FBegQty)/1000 As FBegQty,Sum(v1.FBegBal) As FBegBal,                                                                                                                                                  ";
				sql+="		   Sum(v1.FInQty)/1000 As FInQty,Max(v1.FInPrice) As FInPrice,Sum(v1.FInAmount) As FInAmount,                                                                                                                   ";
				sql+="		   Sum(v1.FOutQty)/1000 As FOutQty,Max(v1.FOutPrice) As FOutPrice,Sum(v1.FOutAmount) As FOutAmount,                                                                                                             ";
				sql+="		   Sum(v1.FInSecQty)/1000 As FInSecQty,Sum(v1.FOutSecQty)/1000 As FOutSecQty,Sum(v1.FBegSecQty)/1000 As FBegSecQty Into #Happen1 From #Happen v1                                                                ";
				sql+="	 Where 1 = 1                                                                                                                                                                                                        ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Group By v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID                                                                                                                                         ";
				sql+="	SET NOCOUNT ON                                                                                                                                                                                                      ";
				sql+="	CREATE TABLE #ItemLevel(                                                                                                                                                                                            ";
				sql+="	 FNumber1 Varchar(355),                                                                                                                                                                                             ";
				sql+="	 FName1 Varchar(355),                                                                                                                                                                                               ";
				sql+="	 FNumber2 Varchar(355),                                                                                                                                                                                             ";
				sql+="	 FName2 Varchar(355),                                                                                                                                                                                               ";
				sql+="	 FItemID int,                                                                                                                                                                                                       ";
				sql+="	 FNumber Varchar(355))                                                                                                                                                                                              ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 INSERT INTO #ItemLevel SELECT                                                                                                                                                                                      ";
				sql+="	 CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1)  END,                                                                                     ";
				sql+="	 '',                                                                                                                                                                                                                ";
				sql+="	 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,                                                                 ";
				sql+="	 CHARINDEX('.',FFullNumber)+1)-1)  END,                                                                                                                                                                             ";
				sql+="	 '',                                                                                                                                                                                                                ";
				sql+="	 FItemID,FNumber FROM RemoteServer.AIS2009.dbo.t_Item                                                                                                                                                               ";
				sql+="	 WHERE FItemClassID=4                                                                                                                                                                                               ";
				sql+="	 AND FDetail=1 AND FNumber>='C.B.301.001' AND FNumber<='C.T.LZ.STW22.Y.300'  And exists (Select FItemID From #Happen Where                                                                                          ";
				sql+="	 #Happen.FItemID=t_Item.FItemID)                                                                                                                                                                                    ";
				sql+="	 UPDATE t0 SET t0.FName1=t1.FName,t0.FName2=t2.FName                                                                                                                                                                ";
				sql+="	  FROM #ItemLevel t0 left join RemoteServer.AIS2009.dbo.t_Item t1 On t0.FNumber1=t1.FNumber  AND t1.FItemClassID=4 AND t1.FDetail=0                                                                                 ";
				sql+="	 left join RemoteServer.AIS2009.dbo.t_Item t2 On t0.FNumber2=t2.FNumber  AND t2.FItemClassID=4 AND t2.FDetail=0                                                                                                     ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	Create Table #Data(                                                                                                                                                                                                 ";
				sql+="	FName1 Varchar(355) Null,                                                                                                                                                                                           ";
				sql+="	FName2 Varchar(355) Null,                                                                                                                                                                                           ";
				sql+="		 FNumber  Varchar(355) null,                                                                                                                                                                                    ";
				sql+="		 FShortNumber  Varchar(355) null,                                                                                                                                                                               ";
				sql+="		 FName  Varchar(355) null,                                                                                                                                                                                      ";
				sql+="		 FModel  Varchar(355) null,                                                                                                                                                                                     ";
				sql+="		 FUnitName  Varchar(355) null,                                                                                                                                                                                  ";
				sql+="		 FQtyDecimal smallint null,                                                                                                                                                                                     ";
				sql+="		 FPriceDecimal smallint null,                                                                                                                                                                                   ";
				sql+="		 FBegQty decimal(28,4),                                                                                                                                                                                         ";
				sql+="		 FBegPrice decimal(28,4),                                                                                                                                                                                       ";
				sql+="		 FBegBal decimal(28,4),                                                                                                                                                                                         ";
				sql+="		 FInQty  decimal(28,4),                                                                                                                                                                                         ";
				sql+="		 FInPrice  decimal(28,4),                                                                                                                                                                                       ";
				sql+="		 FInAmount decimal(28,4),                                                                                                                                                                                       ";
				sql+="		 FOutQty decimal(28,4),                                                                                                                                                                                         ";
				sql+="		 FOutPrice decimal(28,4),                                                                                                                                                                                       ";
				sql+="		 FOutAmount decimal(28,4),                                                                                                                                                                                      ";
				sql+="		 FEndQty decimal(28,4),                                                                                                                                                                                         ";
				sql+="		 FEndPrice decimal(28,4),                                                                                                                                                                                       ";
				sql+="		 FEndAmount decimal(28,4),                                                                                                                                                                                      ";
				sql+="		 FSumSort smallint not null Default(0),                                                                                                                                                                         ";
				sql+="		 FID int IDENTITY,                                                                                                                                                                                              ";
				sql+="	FBegSecQty decimal(28,4) Default(0),FInSecQty decimal(28,4) Default(0),                                                                                                                                             ";
				sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                                                                ";
				sql+="	FBalSecQty decimal(28,4) Default(0)) Insert Into #Data                                                                                                                                                              ";
				sql+="	select FName1,                                                                                                                                                                                                      ";
				sql+="	FName2,                                                                                                                                                                                                             ";
				sql+="	FNumber,'','','','',6,4,sum(FBegQty),case when sum(FBegQty) <> 0 then sum(FBegBal)/sum(FBegQty) else 0 end,sum(FBegBal),sum(FInQty),                                                                                ";
				sql+="	case when sum(FInQty) <> 0 then sum(FInAmount)/ sum(FInQty) else 0 end,sum(FInAmount),sum(FOutQty)                                                                                                                  ";
				sql+="	,case when sum(FOutQty) <> 0 then sum(FOutAmount)/sum(FOutQty) Else 0 end,sum(FOutAmount),sum(FEndQty),case when sum(FEndQty)<>0                                                                                    ";
				sql+="	then sum(FEndAmount)/sum(FEndQty) else 0 end,sum(FEndAmount),                                                                                                                                                       ";
				sql+="	Case   When   Grouping(FName1)=1 THEN 106                                                                                                                                                                           ";
				sql+="	  When   Grouping(FName2)=1 THEN 107                                                                                                                                                                                ";
				sql+="	  When   Grouping(FNumber)=1 THEN 108  Else   0 END                                                                                                                                                                 ";
				sql+="	,Sum (FBegSecQty), Sum(FInSecQty), Sum(FOutSecQty), Sum(FBalSecQty) FROM ( Select tt1.FName1 as FName1,tt1.FName2 as FName2,t1.FNumber as FNumber,                                                                  ";
				sql+="	'' as col1,'' as col2,'' as col3,'' as col4,6 as col5,4 as col6,                                                                                                                                                    ";
				sql+="	SUM(ISNULL(v2.FBegQty,0)) as FBegQty,Case When SUM(ISNULL(v2.FBegQty,0))<>0 then SUM(ISNULL(FBegBal,0))/SUM(cast(ISNULL(FBegQty,0) as decimal(28,4)))                                                               ";
				sql+="	 Else 0 End as FBegPrice,                                                                                                                                                                                           ";
				sql+="	SUM(ISNULL(v2.FBegBal,0)) as FBegBal,SUM(ISNULL(FInQty,0)) as FInQty,Case When SUM(ISNULL(FInQty,0))<>0 Then SUM(ISNULL(FInAmount,0))/SUM(cast(FInQty as decimal(28,4)))                                            ";
				sql+="	 Else 0 End as FInPrice,                                                                                                                                                                                            ";
				sql+="	SUM(ISNULL(FInAmount,0)) as FInAmount,SUM(ISNULL(FOutQty,0)) as FOutQty, Case When SUM(ISNULL(FOutQty,0))<>0 Then SUM(ISNULL(FOutAmount,0))/SUM(cast(ISNULL(FOutQty,0) as decimal(28,4))) Else 0 End as FOutPrice,  ";
				sql+="	SUM(ISNULL(FOutAmount,0)) as FOutAmount,SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0)) as FEndQty,                                                                                             ";
				sql+="	Case When SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))<>0 Then cast((SUM(ISNULL(FBegBal,0))+SUM(ISNULL(FInAmount,0))-SUM(ISNULL(FOutAmount,0))) as                                           ";
				sql+="	decimal(28,4))/cast((SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))) as decimal(28,4)) Else 0 End as FEndPrice,                                                                                ";
				sql+="	Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0)) as FEndAmount,0 as FSumSort,Sum(ISNULL(v2.FBegSecQty,0)) as FBegSecQty,Sum(ISNULL(v2.FInSecQty,0)) as FInSecQty,                          ";
				sql+="	Sum(ISNULL(v2.FOutSecQty,0)) as FOutSecQty,Sum(ISNULL(v2.FBegSecQty,0))+Sum(ISNULL(v2.FInSecQty,0))-Sum(ISNULL(v2.FOutSecQty,0)) as FBalSecQty                                                                      ";
				sql+="	 From #Happen1 v2                                                                                                                                                                                                   ";
				sql+="	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                           ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                                                            ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_AuxItem ta On v2.FAuxPropID=ta.FItemID                                                                                                                                        ";
				sql+="	,#ItemLevel tt1                                                                                                                                                                                                     ";
				sql+="	 Where 1=1                                                                                                                                                                                                          ";
				sql+="	 AND t1.FItemID=tt1.FItemID                                                                                                                                                                                         ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Group By tt1.FName1,tt1.FName2,t1.FNumber                                                                                                                                                                          ";
				sql+="	 ) t Group by FName1,                                                                                                                                                                                               ";
				sql+="	FName2,                                                                                                                                                                                                             ";
				sql+="	FNumber with rollup                                                                                                                                                                                                 ";
				sql+="	 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel,                                                                                                                               ";
				sql+="	 t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal,                                                                                                                             ";
				sql+="	 t1.FInPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FInPrice End),                                                                                                                                      ";
				sql+="	 t1.FOutPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FOutPrice End)                                                                                                                                     ";
				sql+="	 From #DATA t1 Left Join RemoteServer.AIS2009.dbo.t_ICItem t2 On t1.FNumber = t2.FNumber                                                                                                                            ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 On t2.FUnitID=t3.FMeasureUnitID                                                                                                                                ";
				sql+="	 Where t3.FStandard=1                                                                                                                                                                                               ";
				sql+="	update #data set FshortNumber = '合计' where fnumber = '合计'                                                                                                                                                       ";
				sql+="	Update #Data Set  FName1=FName1+'(小计)'  Where FSumSort=107                                                                                                                                                        ";
				sql+="	Update #Data Set  FName2=FName2+'(小计)'  Where FSumSort=108                                                                                                                                                        ";
				sql+="	Update #Data Set FName1='合计' Where FSumSort=106                                                                                                                                                                   ";
				sql+="	Update #Data Set FSumSort=101   Where FSumSort=106                                                                                                                                                                  ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	Select td.*,isnull(td.FName2,'') FName3,isnull(td.FNumber,'')  FNumber3,tm.FName As FSecUnitName From #Data td                                                                                                      ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t On t.FNumber=td.FNumber                                                                                                                                              ";
				sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit tm On t.FSecUnitID=tm.FMeasureUnitID                                                                                                                              ";
				sql+="	 Where 1=1                                                                                                                                                                                                          ";
				sql+="	 Order by td.FID                                                                                                                                                                                                    ";
				sql+="                                                                                                                                                                                                                      ";
				sql+="	 Drop Table #Data                                                                                                                                                                                                   ";
				sql+="	Drop Table #ItemLevel                                                                                                                                                                                               ";
				sql+="	 Drop Table #Happen                                                                                                                                                                                                 ";
				sql+="	 Drop Table #Happen1                                                                                                                                                                                                ";

			//	List<Map> list = inventoryMiniDao.queryCPJHC(sql);
		
				List<Map> maplist = null;

				try {
					maplist =  inventoryMiniDao.queryCPJHC(sql);
					List<String[]> list = new ArrayList<String[]>();

					for (Map<String, Object> map : maplist) {
						String[] arr = new String[map.size()];
						arr[0] = (String) map.get("FName1");
						arr[1] = (String) map.get("FName3");
						arr[2] = (String) map.get("FNumber3");
						arr[3] = (String) map.get("FName");
						arr[4] = (String) map.get("FModel");
						arr[5] = String.valueOf((BigDecimal) map.get("FBegQty"));
						arr[6] = String.valueOf((BigDecimal) map.get("FBegPrice"));
						arr[7] = String.valueOf((BigDecimal) map.get("FBegBal"));
						arr[8] =  String.valueOf((BigDecimal) map.get("FInQty"));
						arr[9] =  String.valueOf((BigDecimal) map.get("FInPrice"));
						arr[10] =  String.valueOf((BigDecimal) map.get("FInAmount"));
						arr[11] = String.valueOf((BigDecimal) map.get("FOutQty"));
						arr[12] = String.valueOf((BigDecimal) map.get("FOutPrice"));
						arr[13] =  String.valueOf((BigDecimal) map.get("FOutAmount"));
						arr[14] = String.valueOf((BigDecimal) map.get("FEndQty")); 
						arr[15] = String.valueOf((BigDecimal) map.get("FEndPrice"));
						arr[16] = String.valueOf((BigDecimal) map.get("FEndQty"));
						list.add(arr);

					}

					String[] columnNames = new String[] { "物料类别1", "物料类别2", "物料代码", "物料名称", "规格型号", "期初结存数量", "期初结存单价", "期初结存金额", "本期收入数量", "本期收入单价", "本期收入金额", "本期发出数量", "本期发出单价",
							"本期发出金额", "期末结存数量", "期末结存单价", "期末结存金额"};
					String titleName = "成品进耗存.xls";

					OutputStream fOut = response.getOutputStream();
					response.setHeader("Content-Disposition", "attachment; filename="
							+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

					ExcelUtils.create(request, list, columnNames, titleName, fOut);

				} catch (Exception ex) {

				}

}
	
	
	
	
	
	
	/**
	 * 
	 * 導出    辅助材料进耗存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_fjhc")
	public void export(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		 String FDate = request.getParameter("FDate");
		 
		 
		//sql+="	-- 辅助材料进耗存                                                                                                                                                              ";
		String sql ="	DECLARE @RQ SMALLDATETIME                                                                                                                                                      ";
		sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                                ";
		sql+="	DECLARE @RQDAY2_M SMALLDATETIME                                                                                                                                                ";
		sql+="                                                                                                                                                                                 ";
		sql+="	DECLARE @YEAR INT                                                                                                                                                              ";
		sql+="	DECLARE @MONTH INT                                                                                                                                                             ";
		//sql+="	--日期参数                                                                                                                                                                     ";
		//sql+="	SET @RQ='2015-12-01'    --查询账期                                                                                                                                             ";
		sql+=" SET @RQ='" + FDate + "-01'";
		sql+="	SET @YEAR=YEAR(@RQ)                                                                                                                                                            ";
		sql+="	SET @MONTH=MONTH(@RQ)                                                                                                                                                          ";
		sql+="                                                                                                                                                                                 ";
		//sql+="	--本月第一天                                                                                                                                                                   ";
		sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                                                                                           ";
		//sql+="	--本月最后一天                                                                                                                                                                 ";
		sql+="	SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                                                                                      ";
		sql+="                                                                                                                                                                                 ";
		sql+="	Set NoCount On                                                                                                                                                                 ";
		sql+="	 Create Table #Happen(                                                                                                                                                         ";
		sql+="			FItemID int Null,                                                                                                                                                      ";
		sql+="			FStockID int Null,                                                                                                                                                     ";
		sql+="			FStockPlaceID int Null,                                                                                                                                                ";
		sql+="			FBatchNo Varchar(200),                                                                                                                                                 ";
		sql+="			FAuxPropID INT NOT NULL DEFAULT(0),                                                                                                                                    ";
		sql+="			FBegQty decimal(28,4),                                                                                                                                                 ";
		sql+="			FBegBal decimal(28,4),                                                                                                                                                 ";
		sql+="			FInQty  decimal(28,4),                                                                                                                                                 ";
		sql+="			FInPrice  decimal(28,4),                                                                                                                                               ";
		sql+="			FInAmount decimal(28,4),                                                                                                                                               ";
		sql+="			FOutQty decimal(28,4),                                                                                                                                                 ";
		sql+="			FOutPrice  decimal(28,4),                                                                                                                                              ";
		sql+="			FOutAmount decimal(28,4),                                                                                                                                              ";
		sql+="	FInSecQty decimal(28,4) Default(0),                                                                                                                                            ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                           ";
		sql+="	FBegSecQty decimal(28,4) Default(0)) Insert Into #Happen                                                                                                                       ";
		sql+="	 Select  v2.FItemID,v2.FStockID,Isnull(v2.FStockPlaceID,0),v2.FBatchNo,v2.FAuxPropID,                                                                                          ";
		sql+="		  Sum (v2.FBegQty), case when t1.FTrack = 81 Then Sum(Round(v2.FBegBal,2) - Round(v2.FBegDiff,2)) Else Sum(Round(v2.FBegBal,2)) End ,0,0,0,0,0,0,                          ";
		sql+="		  0,0,Sum(v2.FSecBegQty)                                                                                                                                                   ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICInvbal v2                                                                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1  On v2.FItemID=t1.FItemID                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FStockPlaceID=t11.FSPID                                                                                             ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Where v2.FYear=@YEAR And v2.FPeriod=@MONTH                                                                                                                                    ";
		sql+="	 And t1.FNumber>='F.BZ.001' And t1. FNumber<='F.ZB.PZJ.ZG.002' And t2.FNumber>='F.GL' AND t2.FNumber<='F.ZB'                                                                   ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v2.FItemID,v2.FStockID,v2.FStockPlaceID,v2.FBatchNo,v2.FAuxPropID,t1.FTrack                                                                                          ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                          ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                       ";
		sql+="	 Case When v1.FTranType In(1,2,5,10,40,100,101,102) And t1.FTrack<>81 Then Max(IsNull(v2.FPrice,0))                                                                            ";
		sql+="		  When v1.FTranType In(1,2,5,10,40,100,101,102,41) And t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0))                                                                      ";
		sql+="		  When v1.FTranType = 41 Then Max(IsNull(v2.FPriceRef,0)) Else 0 End,                                                                                                      ";
		sql+="	 Case When v1.FTranType In(1,2,5,10,40,100,101,102) And t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                 ";
		sql+="		  When v1.FTranType In(1,2,5,10,40,100,101,102,41) And t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0))                                                            ";
		sql+="		  When v1.FTranType =41 Then Sum(IsNull(Round(v2.FAmtRef,2),0)) Else 0 End ,                                                                                               ";
		sql+="	 0,0,0,Sum(IsNull(v2.FSecQty,0)),0,0                                                                                                                                           ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                  ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                           ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                   ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Where (v1.FTranType In (1,2,5,10,40,101,102,41) Or (V1.FTranType=100 And V1.FBillTypeID=12542)) And v1.FDate >=@RQDAY1_M                                                      ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                     ";
		sql+="	 And t1.FNumber>='F.BZ.001' And t1. FNumber<='F.ZB.PZJ.ZG.002' And t2.FNumber>='F.GL' AND t2.FNumber<='F.ZB'                                                                   ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                       ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                    ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                          ";
		sql+="	 0,0,0,                                                                                                                                                                        ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                       ";
		sql+="	 Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0))                                                                                                                        ";
		sql+="		  When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,                                                                                                          ";
		sql+="	 Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                              ";
		sql+="		  When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,                                                                                                ";
		sql+="	0,Sum(IsNull(v2.FSecQty,0)),0                                                                                                                                                  ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                  ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                           ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                   ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Where (v1.FTranType In (21,28,29,43) Or (V1.FTranType=100 And V1.FBillTypeID=12541)) And v1.FDate >=@RQDAY1_M                                                                 ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                     ";
		sql+="	 And t1.FNumber>='F.BZ.001' And t1. FNumber<='F.ZB.PZJ.ZG.002' And t2.FNumber>='F.GL' AND t2.FNumber<='F.ZB'                                                                   ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                       ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                    ";
		sql+="	Insert Into #Happen Select v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,                                      ";
		sql+="		0,0,0,0,0,                                                                                                                                                                 ";
		sql+="		Sum(IsNull(v2.FQty,0)),                                                                                                                                                    ";
		sql+="		Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0)) Else Max(IsNull(v2.FPlanPrice,0)) End,                                                                              ";
		sql+="		Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0)) Else Sum(IsNull(Round(v2.FPlanAmount,2),0)) End,                                                          ";
		sql+="		0,0,Sum(IsNull(v2.FSecQty,0))                                                                                                                                              ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                  ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                           ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FSCStockID=t2.FItemID                                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3  On t1.FStoreUnitID=t3.FMeasureUnitID                                                                                     ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On (Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End)=t11.FSPID                                              ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Where v1.FTranType In (24,41)                                                                                                                                                 ";
		sql+="	 And v1.FDate >=@RQDAY1_M                                                                                                                                                      ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                     ";
		sql+="	 And t1.FNumber>='F.BZ.001' And t1. FNumber<='F.ZB.PZJ.ZG.002' And t2.FNumber>='F.GL' AND t2.FNumber<='F.ZB'                                                                   ";
		sql+="	 And v1.FStatus>0 And v1.FCancelLation=0                                                                                                                                       ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                 ";
		sql+="	 Select v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID,                                                                                                     ";
		sql+="		   Sum(v1.FBegQty) As FBegQty,Sum(v1.FBegBal) As FBegBal,                                                                                                                  ";
		sql+="		   Sum(v1.FInQty) As FInQty,Max(v1.FInPrice) As FInPrice,Sum(v1.FInAmount) As FInAmount,                                                                                   ";
		sql+="		   Sum(v1.FOutQty) As FOutQty,Max(v1.FOutPrice) As FOutPrice,Sum(v1.FOutAmount) As FOutAmount,                                                                             ";
		sql+="		   Sum(v1.FInSecQty) As FInSecQty,Sum(v1.FOutSecQty) As FOutSecQty,Sum(v1.FBegSecQty) As FBegSecQty Into #Happen1 From #Happen v1                                          ";
		sql+="	 Where 1 = 1                                                                                                                                                                   ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID                                                                                                    ";
		sql+="	SET NOCOUNT ON                                                                                                                                                                 ";
		sql+="	CREATE TABLE #ItemLevel(                                                                                                                                                       ";
		sql+="	 FNumber2 Varchar(355),                                                                                                                                                        ";
		sql+="	 FName2 Varchar(355),                                                                                                                                                          ";
		sql+="	 FItemID int,                                                                                                                                                                  ";
		sql+="	 FNumber Varchar(355))                                                                                                                                                         ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 INSERT INTO #ItemLevel SELECT                                                                                                                                                 ";
		sql+="	 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE                                                                            ";
		sql+="	 SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1)  END,                                                                                         ";
		sql+="	 '',                                                                                                                                                                           ";
		sql+="	 FItemID,FNumber FROM RemoteServer.AIS2009.dbo.t_Item                                                                                                                          ";
		sql+="	 WHERE FItemClassID=4                                                                                                                                                          ";
		sql+="	 AND FDetail=1 AND FNumber>='F.BZ.001' AND FNumber<='F.ZB.PZJ.ZG.002'  And exists (Select FItemID From #Happen Where #Happen.FItemID=t_Item.FItemID)                           ";
		sql+="	 UPDATE t0 SET t0.FName2=t2.FName                                                                                                                                              ";
		sql+="	  FROM #ItemLevel t0 left join RemoteServer.AIS2009.dbo.t_Item t2 On t0.FNumber2=t2.FNumber  AND t2.FItemClassID=4 AND t2.FDetail=0                                            ";
		sql+="                                                                                                                                                                                 ";
		sql+="	Create Table #Data(                                                                                                                                                            ";
		sql+="	FName2 Varchar(355) Null,                                                                                                                                                      ";
		sql+="		 FNumber  Varchar(355) null,                                                                                                                                               ";
		sql+="		 FShortNumber  Varchar(355) null,                                                                                                                                          ";
		sql+="		 FName  Varchar(355) null,                                                                                                                                                 ";
		sql+="		 FModel  Varchar(355) null,                                                                                                                                                ";
		sql+="		 FUnitName  Varchar(355) null,                                                                                                                                             ";
		sql+="		 FQtyDecimal smallint null,                                                                                                                                                ";
		sql+="		 FPriceDecimal smallint null,                                                                                                                                              ";
		sql+="		 FBegQty decimal(28,4),                                                                                                                                                    ";
		sql+="		 FBegPrice decimal(28,4),                                                                                                                                                  ";
		sql+="		 FBegBal decimal(28,4),                                                                                                                                                    ";
		sql+="		 FInQty  decimal(28,4),                                                                                                                                                    ";
		sql+="		 FInPrice  decimal(28,4),                                                                                                                                                  ";
		sql+="		 FInAmount decimal(28,4),                                                                                                                                                  ";
		sql+="		 FOutQty decimal(28,4),                                                                                                                                                    ";
		sql+="		 FOutPrice decimal(28,4),                                                                                                                                                  ";
		sql+="		 FOutAmount decimal(28,4),                                                                                                                                                 ";
		sql+="		 FEndQty decimal(28,4),                                                                                                                                                    ";
		sql+="		 FEndPrice decimal(28,4),                                                                                                                                                  ";
		sql+="		 FEndAmount decimal(28,4),                                                                                                                                                 ";
		sql+="		 FSumSort smallint not null Default(0),                                                                                                                                    ";
		sql+="		 FID int IDENTITY,                                                                                                                                                         ";
		sql+="	FBegSecQty decimal(28,4) Default(0),FInSecQty decimal(28,4) Default(0),                                                                                                        ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                           ";
		sql+="	FBalSecQty decimal(28,4) Default(0)) Insert Into #Data                                                                                                                         ";
		sql+="	select FName2,                                                                                                                                                                 ";
		sql+="	FNumber,'','','','',6,4,sum(FBegQty)/1000,case when sum(FBegQty) <> 0 then sum(FBegBal)/sum(FBegQty) else 0 end,sum(FBegBal),sum(FInQty)/1000,                                           ";
		sql+="	case when sum(FInQty) <> 0 then sum(FInAmount)/ sum(FInQty) else 0 end,sum(FInAmount),sum(FOutQty)/1000                                                                             ";
		sql+="	,case when sum(FOutQty) <> 0 then sum(FOutAmount)/sum(FOutQty) Else 0 end,sum(FOutAmount),sum(FEndQty)/1000,case when sum(FEndQty)<>0                                               ";
		sql+="	then sum(FEndAmount)/sum(FEndQty) else 0 end,sum(FEndAmount),                                                                                                                  ";
		sql+="	Case   When   Grouping(FName2)=1 THEN 107                                                                                                                                      ";
		sql+="	  When   Grouping(FNumber)=1 THEN 108  Else   0 END                                                                                                                            ";
		sql+="	,Sum (FBegSecQty)/1000, Sum(FInSecQty)/1000, Sum(FOutSecQty)/1000, Sum(FBalSecQty)/1000 FROM ( Select tt1.FName2 as FName2,t1.FNumber as FNumber,'' as col1,'' as col2,                            ";
		sql+="	'' as col3,'' as col4,6 as col5,4 as col6,                                                                                                                                     ";
		sql+="	SUM(ISNULL(v2.FBegQty,0)) as FBegQty/1000,Case When SUM(ISNULL(v2.FBegQty,0))<>0 then SUM(ISNULL(FBegBal,0))/SUM(cast(ISNULL(FBegQty,0) as decimal(28,4))) Else 0 End as FBegPrice, ";
		sql+="	SUM(ISNULL(v2.FBegBal,0)) as FBegBal,SUM(ISNULL(FInQty,0))/1000 as FInQty,Case When SUM(ISNULL(FInQty,0))<>0 Then SUM(ISNULL(FInAmount,0))/SUM(cast(FInQty as decimal(28,4)))       ";
		sql+="	Else 0 End as FInPrice,                                                                                                                                                        ";
		sql+="	SUM(ISNULL(FInAmount,0)) as FInAmount,SUM(ISNULL(FOutQty,0))/1000 as FOutQty, Case When SUM(ISNULL(FOutQty,0))<>0                                                                   ";
		sql+="	Then SUM(ISNULL(FOutAmount,0))/SUM(cast(ISNULL(FOutQty,0) as decimal(28,4))) Else 0 End as FOutPrice,                                                                          ";
		sql+="	SUM(ISNULL(FOutAmount,0)) as FOutAmount,SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))/1000 as FEndQty,                                                        ";
		sql+="	Case When SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))<>0 Then                                                                                          ";
		sql+="	cast((SUM(ISNULL(FBegBal,0))+SUM(ISNULL(FInAmount,0))-SUM(ISNULL(FOutAmount,0))) as                                                                                            ";
		sql+="	decimal(28,4))/cast((SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))) as decimal(28,4)) Else 0 End as FEndPrice,                                           ";
		sql+="	Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0)) as FEndAmount,0 as FSumSort,Sum(ISNULL(v2.FBegSecQty,0)) as                                          ";
		sql+="	FBegSecQty,Sum(ISNULL(v2.FInSecQty,0)) as FInSecQty,Sum(ISNULL(v2.FOutSecQty,0)) as FOutSecQty,                                                                                ";
		sql+="	Sum(ISNULL(v2.FBegSecQty,0))+Sum(ISNULL(v2.FInSecQty,0))-Sum(ISNULL(v2.FOutSecQty,0)) as FBalSecQty                                                                            ";
		sql+="	 From #Happen1 v2                                                                                                                                                              ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_AuxItem ta On v2.FAuxPropID=ta.FItemID                                                                                                   ";
		sql+="	,#ItemLevel tt1                                                                                                                                                                ";
		sql+="	 Where 1=1                                                                                                                                                                     ";
		sql+="	 AND t1.FItemID=tt1.FItemID                                                                                                                                                    ";
		sql+="                                                                                                                                                                                 ";
		sql+="	 Group By tt1.FName2,t1.FNumber                                                                                                                                                ";
		sql+="	 ) t Group by FName2,                                                                                                                                                          ";
		sql+="	FNumber with rollup                                                                                                                                                            ";
		sql+="	 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel,                                                                                          ";
		sql+="	 t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal,                                                                                        ";
		sql+="	 t1.FInPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FInPrice End),                                                                                                 ";
		sql+="	 t1.FOutPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FOutPrice End)                                                                                                ";
		sql+="	 From #DATA t1 Left Join RemoteServer.AIS2009.dbo.t_ICItem t2 On t1.FNumber = t2.FNumber                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 On t2.FUnitID=t3.FMeasureUnitID                                                                                           ";
		sql+="	 Where t3.FStandard=1                                                                                                                                                          ";
		sql+="	update #data set FshortNumber = '合计' where fnumber = '合计'                                                                                                                  ";
		sql+="	Update #Data Set  FName2=FName2+'(小计)'  Where FSumSort=108                                                                                                                   ";
		sql+="	Update #Data Set FName2='合计' Where FSumSort=107                                                                                                                              ";
		sql+="	Update #Data Set FSumSort=101   Where FSumSort=107                                                                                                                             ";
		sql+="                                                                                                                                                                                 ";
		sql+="	Select td.*,tm.FName As FSecUnitName From #Data td                                                                                                                             ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t On t.FNumber=td.FNumber                                                                                                         ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit tm On t.FSecUnitID=tm.FMeasureUnitID                                                                                         ";
		sql+="	Where 1=1                                                                                                                                                                      ";
		sql+="	 And td.FSumSort>100                                                                                                                                                           ";
		sql+="	 Order by td.FID Drop Table #Data                                                                                                                                              ";
		sql+="	Drop Table #ItemLevel                                                                                                                                                          ";
		sql+="	 Drop Table #Happen                                                                                                                                                            ";
		sql+="	 Drop Table #Happen1                                                                                                                                                           ";
  

		//List<Map> list = inventoryMiniDao.queryFJHC(sql);

		List<Map> maplist = null;

		try {
			maplist = inventoryMiniDao.queryFJHC(sql);
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FName2");
				arr[1] = String.valueOf((BigDecimal) map.get("FBegQty"));
				arr[2] = String.valueOf((BigDecimal) map.get("FBegPrice"));
				arr[3] = String.valueOf((BigDecimal) map.get("FBegBal"));
				arr[4] =  String.valueOf((BigDecimal) map.get("FInQty"));
				arr[5] =  String.valueOf((BigDecimal) map.get("FInPrice"));
				arr[6] =  String.valueOf((BigDecimal) map.get("FInAmount"));
				arr[7] = String.valueOf((BigDecimal) map.get("FOutQty"));
				arr[8] = String.valueOf((BigDecimal) map.get("FOutPrice"));
				arr[9] =  String.valueOf((BigDecimal) map.get("FOutAmount"));
				arr[10] = String.valueOf((BigDecimal) map.get("FEndQty")); 
				arr[11] = String.valueOf((BigDecimal) map.get("FEndPrice"));
				arr[12] = String.valueOf((BigDecimal) map.get("FEndQty"));
				list.add(arr);

			}

			String[] columnNames = new String[] { "物料类别", "期初结存数量", "期初结存单价", "期初结存金额", "本期收入数量", "本期收入单价", "本期收入金额", "本期发出数量", "本期发出单价",
					"本期发出金额", "期末结存数量", "期末结存单价", "期末结存金额"};
			String titleName = "辅助材料进耗存.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}
	
	/**
	 * 
	 * 導出    原材料进耗存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_yjhc")
	public void export_yjhc(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		
		String FDate = request.getParameter("FDate");
		//sql+="	-- 原材料进耗存                                                                                                                                                                                                                                                 ";                                                                                                                                                                                                                                                                                         
		String sql ="	DECLARE @RQ SMALLDATETIME                                                                                                                                                                                                                                       ";
		sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                                                                                                                 ";
		sql+="	DECLARE @RQDAY2_M SMALLDATETIME                                                                                                                                                                                                                                 ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	DECLARE @YEAR INT                                                                                                                                                                                                                                               ";
		sql+="	DECLARE @MONTH INT                                                                                                                                                                                                                                              ";
		//sql+="	--日期参数                                                                                                                                                                                                                                                      ";
		//sql+="	SET @RQ='2016-01-01'    --查询账期                                                                                                                                                                                                                              ";
		sql+=" SET @RQ='" + FDate + "-01'";
		sql+="	SET @YEAR=YEAR(@RQ)                                                                                                                                                                                                                                             ";
		sql+="	SET @MONTH=MONTH(@RQ)                                                                                                                                                                                                                                           ";
		sql+="                                                                                                                                                                                                                                                                  ";
		//sql+="	--本月第一天                                                                                                                                                                                                                                                    ";
		sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                                                                                                                                                                            ";
		//sql+="	--本月最后一天                                                                                                                                                                                                                                                  ";
		sql+="	SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                                                                                                                                                                       ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	Set NoCount On                                                                                                                                                                                                                                                  ";
		sql+="	 Create Table #Happen(                                                                                                                                                                                                                                          ";
		sql+="			FItemID int Null,                                                                                                                                                                                                                                       ";
		sql+="			FStockID int Null,                                                                                                                                                                                                                                      ";
		sql+="			FStockPlaceID int Null,                                                                                                                                                                                                                                 ";
		sql+="			FBatchNo Varchar(200),                                                                                                                                                                                                                                  ";
		sql+="			FAuxPropID INT NOT NULL DEFAULT(0),                                                                                                                                                                                                                     ";
		sql+="			FBegQty decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="			FBegBal decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="			FInQty  decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="			FInPrice  decimal(28,4),                                                                                                                                                                                                                                ";
		sql+="			FInAmount decimal(28,4),                                                                                                                                                                                                                                ";
		sql+="			FOutQty decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="			FOutPrice  decimal(28,4),                                                                                                                                                                                                                               ";
		sql+="			FOutAmount decimal(28,4),                                                                                                                                                                                                                               ";
		sql+="	FInSecQty decimal(28,4) Default(0),                                                                                                                                                                                                                             ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                                                                                                            ";
		sql+="	FBegSecQty decimal(28,4) Default(0)) Insert Into #Happen                                                                                                                                                                                                        ";
		sql+="	 Select  v2.FItemID,v2.FStockID,Isnull(v2.FStockPlaceID,0),v2.FBatchNo,v2.FAuxPropID,                                                                                                                                                                           ";
		sql+="		  Sum (v2.FBegQty), case when t1.FTrack = 81 Then Sum(Round(v2.FBegBal,2) - Round(v2.FBegDiff,2)) Else Sum(Round(v2.FBegBal,2)) End ,0,0,0,0,0,0,                                                                                                           ";
		sql+="		  0,0,Sum(v2.FSecBegQty)                                                                                                                                                                                                                                    ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICInvbal v2                                                                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1  On v2.FItemID=t1.FItemID                                                                                                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FStockPlaceID=t11.FSPID                                                                                                                                                                              ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Where v2.FYear=@YEAR And v2.FPeriod=@MONTH                                                                                                                                                                                                                     ";
		sql+="	 And t1.FNumber>='Y.B.L.301.02' And t1. FNumber<='Y.ZT.C1921.01'                                                                                                                                                                                                ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v2.FItemID,v2.FStockID,v2.FStockPlaceID,v2.FBatchNo,v2.FAuxPropID,t1.FTrack                                                                                                                                                                           ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                                                                                                           ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                                                                        ";
		sql+="	 Case When v1.FTranType In(1,2,5,10,40,100,101,102) And t1.FTrack<>81 Then Max(IsNull(v2.FPrice,0))                                                                                                                                                             ";
		sql+="		  When v1.FTranType In(1,2,5,10,40,100,101,102,41) And t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0))                                                                                                                                                       ";
		sql+="		  When v1.FTranType = 41 Then Max(IsNull(v2.FPriceRef,0)) Else 0 End,                                                                                                                                                                                       ";
		sql+="	 Case When v1.FTranType In(1,2,5,10,40,100,101,102) And t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                                                                  ";
		sql+="		  When v1.FTranType In(1,2,5,10,40,100,101,102,41) And t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0))                                                                                                                                             ";
		sql+="		  When v1.FTranType =41 Then Sum(IsNull(Round(v2.FAmtRef,2),0)) Else 0 End ,                                                                                                                                                                                ";
		sql+="	 0,0,0,Sum(IsNull(v2.FSecQty,0)),0,0                                                                                                                                                                                                                            ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                                                                   ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Where (v1.FTranType In (1,2,5,10,40,101,102,41) Or (V1.FTranType=100 And V1.FBillTypeID=12542)) And v1.FDate >=@RQDAY1_M                                                                                                                                       ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                                                                                                      ";
		sql+="	 And t1.FNumber>='Y.B.L.301.02' And t1. FNumber<='Y.ZT.C1921.01'                                                                                                                                                                                                ";
		sql+="	 And v1.FCancelLation=0                                                                                                                                                                                                                                         ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                                                                     ";
		sql+="	 Insert Into #Happen Select v2.FItemID,t2.FItemID,Isnull(v2.FDCSPID,0),v2.FBatchNo,v2.FAuxPropID,0,0,                                                                                                                                                           ";
		sql+="	 0,0,0,                                                                                                                                                                                                                                                         ";
		sql+="	 Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                                                                        ";
		sql+="	 Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0))                                                                                                                                                                                                         ";
		sql+="		  When t1.FTrack=81 Then Max(IsNull(v2.FPlanPrice,0)) Else 0 End,                                                                                                                                                                                           ";
		sql+="	 Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0))                                                                                                                                                                                               ";
		sql+="		  When t1.FTrack=81 Then Sum(IsNull(Round(v2.FPlanAmount,2),0)) Else 0 End,                                                                                                                                                                                 ";
		sql+="	0,Sum(IsNull(v2.FSecQty,0)),0                                                                                                                                                                                                                                   ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                                                                   ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FDCStockID=t2.FItemID                                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On v2.FDCSPID=t11.FSPID                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Where (v1.FTranType In (21,28,29,43) Or (V1.FTranType=100 And V1.FBillTypeID=12541)) And v1.FDate >=@RQDAY1_M                                                                                                                                                  ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                                                                                                      ";
		sql+="	 And t1.FNumber>='Y.B.L.301.02' And t1. FNumber<='Y.ZT.C1921.01'                                                                                                                                                                                                ";
		sql+="	 And v1.FCancelLation=0                                                                                                                                                                                                                                         ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v2.FItemID,t2.FItemID,v2.FDCSPID,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                                                                     ";
		sql+="	Insert Into #Happen Select v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,                                                                                                                       ";
		sql+="		0,0,0,0,0,                                                                                                                                                                                                                                                  ";
		sql+="		Sum(IsNull(v2.FQty,0)),                                                                                                                                                                                                                                     ";
		sql+="		Case When t1.FTrack<>81 Then  Max(IsNull(v2.FPrice,0)) Else Max(IsNull(v2.FPlanPrice,0)) End,                                                                                                                                                               ";
		sql+="		Case When t1.FTrack<>81 Then  Sum(IsNull(Round(v2.FAmount,2),0)) Else Sum(IsNull(Round(v2.FPlanAmount,2),0)) End,                                                                                                                                           ";
		sql+="		0,0,Sum(IsNull(v2.FSecQty,0))                                                                                                                                                                                                                               ";
		sql+="	 From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                                                                                                                                   ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2  On v1.FInterID=v2.FInterID                                                                                                                                                                            ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FSCStockID=t2.FItemID                                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3  On t1.FStoreUnitID=t3.FMeasureUnitID                                                                                                                                                                      ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_StockPlace t11 On (Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End)=t11.FSPID                                                                                                                               ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Where v1.FTranType In (24,41)                                                                                                                                                                                                                                  ";
		sql+="	 And v1.FDate >=@RQDAY1_M                                                                                                                                                                                                                                       ";
		sql+="	 And v1.FDate <@RQDAY2_M+1                                                                                                                                                                                                                                      ";
		sql+="	 And t1.FNumber>='Y.B.L.301.02' And t1. FNumber<='Y.ZT.C1921.01'                                                                                                                                                                                                ";
		sql+="	 And v1.FCancelLation=0                                                                                                                                                                                                                                         ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v2.FItemID,t2.FItemID,Case When v1.FTranType=41 Then v2.FSCSPID Else v2.FDCSPID End,v2.FBatchNo,v2.FAuxPropID,v1.FTranType,t1.FTrack                                                                                                                  ";
		sql+="	 Select v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID,                                                                                                                                                                                      ";
		sql+="		   Sum(v1.FBegQty) As FBegQty,Sum(v1.FBegBal) As FBegBal,                                                                                                                                                                                                   ";
		sql+="		   Sum(v1.FInQty) As FInQty,Max(v1.FInPrice) As FInPrice,Sum(v1.FInAmount) As FInAmount,                                                                                                                                                                    ";
		sql+="		   Sum(v1.FOutQty) As FOutQty,Max(v1.FOutPrice) As FOutPrice,Sum(v1.FOutAmount) As FOutAmount,                                                                                                                                                              ";
		sql+="		   Sum(v1.FInSecQty) As FInSecQty,Sum(v1.FOutSecQty) As FOutSecQty,Sum(v1.FBegSecQty) As FBegSecQty Into #Happen1 From #Happen v1                                                                                                                           ";
		sql+="	 Where 1 = 1                                                                                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By v1.FItemID,v1.FStockID,v1.FStockPlaceID,v1.FBatchNo,v1.FAuxPropID                                                                                                                                                                                     ";
		sql+="	SET NOCOUNT ON                                                                                                                                                                                                                                                  ";
		sql+="	CREATE TABLE #ItemLevel(                                                                                                                                                                                                                                        ";
		sql+="	 FNumber2 Varchar(355),                                                                                                                                                                                                                                         ";
		sql+="	 FName2 Varchar(355),                                                                                                                                                                                                                                           ";
		sql+="	 FItemID int,                                                                                                                                                                                                                                                   ";
		sql+="	 FNumber Varchar(355))                                                                                                                                                                                                                                          ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 INSERT INTO #ItemLevel SELECT                                                                                                                                                                                                                                  ";
		sql+="	 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1)  END,                                                                       ";
		sql+="	 '',                                                                                                                                                                                                                                                            ";
		sql+="	 FItemID,FNumber FROM RemoteServer.AIS2009.dbo.t_Item                                                                                                                                                                                                           ";
		sql+="	 WHERE FItemClassID=4                                                                                                                                                                                                                                           ";
		sql+="	 AND FDetail=1 AND FNumber>='Y.B.L.301.02' AND FNumber<='Y.ZT.C1921.01'  And exists (Select FItemID From #Happen Where #Happen.FItemID=t_Item.FItemID)                                                                                                          ";
		sql+="	 UPDATE t0 SET t0.FName2=t2.FName                                                                                                                                                                                                                               ";
		sql+="	  FROM #ItemLevel t0 left join RemoteServer.AIS2009.dbo.t_Item t2 On t0.FNumber2=t2.FNumber  AND t2.FItemClassID=4 AND t2.FDetail=0                                                                                                                             ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	Create Table #Data(                                                                                                                                                                                                                                             ";
		sql+="	FName2 Varchar(355) Null,                                                                                                                                                                                                                                       ";
		sql+="		 FNumber  Varchar(355) null,                                                                                                                                                                                                                                ";
		sql+="		 FShortNumber  Varchar(355) null,                                                                                                                                                                                                                           ";
		sql+="		 FName  Varchar(355) null,                                                                                                                                                                                                                                  ";
		sql+="		 FModel  Varchar(355) null,                                                                                                                                                                                                                                 ";
		sql+="		 FUnitName  Varchar(355) null,                                                                                                                                                                                                                              ";
		sql+="		 FQtyDecimal smallint null,                                                                                                                                                                                                                                 ";
		sql+="		 FPriceDecimal smallint null,                                                                                                                                                                                                                               ";
		sql+="		 FBegQty decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FBegPrice decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FBegBal decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FInQty  decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FInPrice  decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FInAmount decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FOutQty decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FOutPrice decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FOutAmount decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="		 FEndQty decimal(28,4),                                                                                                                                                                                                                                     ";
		sql+="		 FEndPrice decimal(28,4),                                                                                                                                                                                                                                   ";
		sql+="		 FEndAmount decimal(28,4),                                                                                                                                                                                                                                  ";
		sql+="		 FSumSort smallint not null Default(0),                                                                                                                                                                                                                     ";
		sql+="		 FID int IDENTITY,                                                                                                                                                                                                                                          ";
		sql+="	FBegSecQty decimal(28,4) Default(0),FInSecQty decimal(28,4) Default(0),                                                                                                                                                                                         ";
		sql+="	FOutSecQty decimal(28,4) Default(0),                                                                                                                                                                                                                            ";
		sql+="	FBalSecQty decimal(28,4) Default(0)) Insert Into #Data                                                                                                                                                                                                          ";
		sql+="	select FName2,                                                                                                                                                                                                                                                  ";
		sql+="	FNumber,'','','','',6,4,sum(FBegQty),case when sum(FBegQty) <> 0 then sum(FBegBal)/sum(FBegQty) else 0 end,sum(FBegBal),sum(FInQty),case when sum(FInQty) <> 0 then sum(FInAmount)/ sum(FInQty) else 0 end,sum(FInAmount),sum(FOutQty)                          ";
		sql+="	,case when sum(FOutQty) <> 0 then sum(FOutAmount)/sum(FOutQty) Else 0 end,sum(FOutAmount),sum(FEndQty),case when sum(FEndQty)<>0 then sum(FEndAmount)/sum(FEndQty) else 0 end,sum(FEndAmount),                                                                  ";
		sql+="	Case   When   Grouping(FName2)=1 THEN 107                                                                                                                                                                                                                       ";
		sql+="	  When   Grouping(FNumber)=1 THEN 108  Else   0 END                                                                                                                                                                                                             ";
		sql+="	,Sum (FBegSecQty), Sum(FInSecQty), Sum(FOutSecQty), Sum(FBalSecQty) FROM ( Select tt1.FName2 as FName2,t1.FNumber as FNumber,'' as col1,'' as col2,'' as col3,'' as col4,6 as col5,4 as col6,                                                                   ";
		sql+="	SUM(ISNULL(v2.FBegQty,0)) as FBegQty,Case When SUM(ISNULL(v2.FBegQty,0))<>0 then SUM(ISNULL(FBegBal,0))/SUM(cast(ISNULL(FBegQty,0) as decimal(28,4))) Else 0 End as FBegPrice,                                                                                  ";
		sql+="	SUM(ISNULL(v2.FBegBal,0)) as FBegBal,SUM(ISNULL(FInQty,0)) as FInQty,Case When SUM(ISNULL(FInQty,0))<>0 Then SUM(ISNULL(FInAmount,0))/SUM(cast(FInQty as decimal(28,4))) Else 0 End as FInPrice,                                                                ";
		sql+="	SUM(ISNULL(FInAmount,0)) as FInAmount,SUM(ISNULL(FOutQty,0)) as FOutQty, Case When SUM(ISNULL(FOutQty,0))<>0 Then SUM(ISNULL(FOutAmount,0))/SUM(cast(ISNULL(FOutQty,0) as decimal(28,4))) Else 0 End as FOutPrice,                                              ";
		sql+="	SUM(ISNULL(FOutAmount,0)) as FOutAmount,SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0)) as FEndQty,                                                                                                                                         ";
		sql+="	Case When SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))<>0 Then cast((SUM(ISNULL(FBegBal,0))+SUM(ISNULL(FInAmount,0))-SUM(ISNULL(FOutAmount,0))) as decimal(28,4))/cast((SUM(ISNULL(FBegQty,0))+                                          ";
		sql+="	SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))) as decimal(28,4)) Else 0 End as FEndPrice,                                                                                                                                                                        ";
		sql+="	Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0)) as FEndAmount,0 as FSumSort,Sum(ISNULL(v2.FBegSecQty,0)) as FBegSecQty,Sum(ISNULL(v2.FInSecQty,0)) as FInSecQty,Sum(ISNULL(v2.FOutSecQty,0)) as FOutSecQty,                           ";
		sql+="	Sum(ISNULL(v2.FBegSecQty,0))+Sum(ISNULL(v2.FInSecQty,0))-Sum(ISNULL(v2.FOutSecQty,0)) as FBalSecQty                                                                                                                                                             ";
		sql+="	 From #Happen1 v2                                                                                                                                                                                                                                               ";
		sql+="	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID=t1.FItemID                                                                                                                                                                                       ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_Stock t2 On v2.FStockID=t2.FItemID                                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_AuxItem ta On v2.FAuxPropID=ta.FItemID                                                                                                                                                                                    ";
		sql+="	,#ItemLevel tt1                                                                                                                                                                                                                                                 ";
		sql+="	 Where 1=1                                                                                                                                                                                                                                                      ";
		sql+="	 AND t1.FItemID=tt1.FItemID                                                                                                                                                                                                                                     ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	 Group By tt1.FName2,t1.FNumber                                                                                                                                                                                                                                 ";
		sql+="	 Having NOT (SUM(ISNULL(FBegQty,0))+SUM(ISNULL(FInQty,0))-SUM(ISNULL(FOutQty,0))=0 AND Sum(ISNULL(FBegBal,0))+Sum(ISNULL(FInAmount,0))-Sum(ISNULL(FOutAmount,0))=0)                                                                                             ";
		sql+="	 ) t Group by FName2,                                                                                                                                                                                                                                           ";
		sql+="	FNumber with rollup                                                                                                                                                                                                                                             ";
		sql+="	 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel,                                                                                                                                                                           ";
		sql+="	 t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal,                                                                                                                                                                         ";
		sql+="	 t1.FInPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FInPrice End),                                                                                                                                                                                  ";
		sql+="	 t1.FOutPrice=(Case When t2.FTrack=81 Then t2.FPlanPrice Else t1.FOutPrice End)                                                                                                                                                                                 ";
		sql+="	 From #DATA t1 Left Join RemoteServer.AIS2009.dbo.t_ICItem t2 On t1.FNumber = t2.FNumber                                                                                                                                                                        ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 On t2.FUnitID=t3.FMeasureUnitID                                                                                                                                                                            ";
		sql+="	 Where t3.FStandard=1                                                                                                                                                                                                                                           ";
		sql+="	update #data set FshortNumber = '合计' where fnumber = '合计'                                                                                                                                                                                                   ";
		sql+="	Update #Data Set  FName2=FName2+'(小计)'  Where FSumSort=108                                                                                                                                                                                                    ";
		sql+="	Update #Data Set FName2='合计' Where FSumSort=107                                                                                                                                                                                                               ";
		sql+="	Update #Data Set FSumSort=101   Where FSumSort=107                                                                                                                                                                                                              ";
		sql+="                                                                                                                                                                                                                                                                  ";
		sql+="	Select td.*,tm.FName As FSecUnitName From #Data td                                                                                                                                                                                                              ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_ICItem t On t.FNumber=td.FNumber                                                                                                                                                                                          ";
		sql+="	 Left Join RemoteServer.AIS2009.dbo.t_MeasureUnit tm On t.FSecUnitID=tm.FMeasureUnitID                                                                                                                                                                          ";
		sql+="	Where 1=1                                                                                                                                                                                                                                                       ";
		sql+="	 And td.FSumSort>100                                                                                                                                                                                                                                            ";
		sql+="	 Order by td.FID Drop Table #Data                                                                                                                                                                                                                               ";
		sql+="	Drop Table #ItemLevel                                                                                                                                                                                                                                           ";
		sql+="	 Drop Table #Happen                                                                                                                                                                                                                                             ";
		sql+="	 Drop Table #Happen1                                                                                                                                                                                                                                            ";
		                                                                                                                                                                                                                                                                       

		//List<Map> list = inventoryMiniDao.queryFJHC(sql);

		List<Map> maplist = null;

		try {
			maplist = inventoryMiniDao.queryYJHC(sql);
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FName2");
				arr[1] = String.valueOf((BigDecimal) map.get("FBegQty"));
				arr[2] = String.valueOf((BigDecimal) map.get("FBegPrice"));
				arr[3] = String.valueOf((BigDecimal) map.get("FBegBal"));
				arr[4] =  String.valueOf((BigDecimal) map.get("FInQty"));
				arr[5] =  String.valueOf((BigDecimal) map.get("FInPrice"));
				arr[6] =  String.valueOf((BigDecimal) map.get("FInAmount"));
				arr[7] = String.valueOf((BigDecimal) map.get("FOutQty"));
				arr[8] = String.valueOf((BigDecimal) map.get("FOutPrice"));
				arr[9] =  String.valueOf((BigDecimal) map.get("FOutAmount"));
				arr[10] = String.valueOf((BigDecimal) map.get("FEndQty")); 
				arr[11] = String.valueOf((BigDecimal) map.get("FEndPrice"));
				arr[12] = String.valueOf((BigDecimal) map.get("FEndQty"));
				list.add(arr);

			}

			String[] columnNames = new String[] { "物料类别", "期初结存数量", "期初结存单价", "期初结存金额", "本期收入数量", "本期收入单价", "本期收入金额", "本期发出数量", "本期发出单价",
					"本期发出金额", "期末结存数量", "期末结存单价", "期末结存金额"};
			String titleName = "原材料进耗存.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	/**
	 * easyui AJAX请求数据   其他增加
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid2")
	public void datagrid2(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
	   
		 String FDate = request.getParameter("FDate");
		 String FName = request.getParameter("FName");
		 


		 String sql ="	DECLARE @RQ SMALLDATETIME                                                                                     ";
		 sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                               ";
		 sql+="	DECLARE @RQDAY2_M SMALLDATETIME                                                                               ";
		 sql+="	DECLARE @YEAR INT                                                                                             ";
		 sql+="	DECLARE @MONTH INT                                                                                            ";
		 sql+="	DECLARE @FName NVARCHAR(36)                                                                                   ";
		 sql+="                                                                                                                ";
		// sql+="	SET @RQ='2015-12-01'                                                                                          ";
		// sql+="	SET @FName = '%镀铬钢带%'                                                                                     ";
			sql += " SET @RQ='" + FDate + "-01'";
			if(StringUtil.isNotEmpty(FName)){
				sql += " SET @FName = '%" + FName +"%'";
				
			}
		 
		 
		 sql+="	SET @YEAR=YEAR(@RQ)                                                                                           ";
		 sql+="	SET @MONTH=MONTH(@RQ)                                                                                         ";
		 sql+="                                                                                                                ";
		 sql+="                                                                                                                ";
		 sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                          ";
		 sql+="	SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                     ";
		 sql+="                                                                                                                ";
		 sql+="                                                                                                                ";
		 sql+="	select t2.FBatchNo,t3.FName,t3.FModel,Convert(decimal(18,4),t2.FAuxQty) FAuxQty,t1.FTranType  from RemoteServer.AIS2009.dbo.ICStockBill t1   ";
		 sql+="	inner join RemoteServer.AIS2009.dbo.ICStockBillEntry t2 on t1.FInterID = t2.FInterID                          ";
		 sql+="	left join RemoteServer.AIS2009.dbo.t_ICItem t3 on t2.FItemID = t3.FItemID                                     ";
		 sql+="	where ( t1.FTranType=2 and isnull(t1.FCheckerID,0)>0 and (t1.FCancellation = 0))                              ";
		 sql+="	and t1.FDate <= @RQDAY2_M                                                                                     ";
		 sql+="	and t1.FDate >= @RQDAY1_M                                                                                     ";
		
		 sql+="	and t3.FName like @FName                                                                                ";
		 
		 if(StringUtil.isNotEmpty(FName)){
			 if(FName.contains("不锈钢")){
				 sql+="	and t2.FBatchNo like 'SG%'                                                                                    ";
 
				 
			 }else{
				 sql+="	and t2.FBatchNo like 'YG%'                                                                                    ";

				 
			 }
			 
		 }
		 
 

		
		  List<Map> list = inventoryMiniDao.queryQTZJ(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	/**
	 * easyui AJAX请求数据  其他减少
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid3")
	public void datagrid3(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
	   
		 String FDate = request.getParameter("FDate");
		 String FName = request.getParameter("FName");
		 
		 
		String sql ="	DECLARE @RQ SMALLDATETIME                                                                                   ";
			sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                             ";
			sql+="	DECLARE @RQDAY2_M SMALLDATETIME                                                                             ";
			sql+="	DECLARE @YEAR INT                                                                                           ";
			sql+="	DECLARE @MONTH INT                                                                                          ";
			sql+="	DECLARE @FName NVARCHAR(36)                                                                                 ";
			sql+="                                                                                                              ";
			
			//sql+="	SET @RQ='2015-12-01'                                                                                        ";
			//sql+="	SET @FName = '%镀铬钢带%'                                                                                   ";
			
			sql += " SET @RQ='" + FDate + "-01'";
			if(StringUtil.isNotEmpty(FName)){
				sql += " SET @FName = '%" + FName +"%'";
				
			}
			
			sql+="	SET @YEAR=YEAR(@RQ)                                                                                         ";
			sql+="	SET @MONTH=MONTH(@RQ)                                                                                       ";
			sql+="                                                                                                              ";
			sql+="                                                                                                              ";
			sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                        ";
			sql+="	SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                   ";
			sql+="                                                                                                              ";
			sql+="                                                                                                              ";
			sql+="	select FBatchNo,FName,FModel,FTranType,Convert(decimal(18,4),sum(FAuxQty)) FQty from (                      ";
			sql+="	select t2.FBatchNo,t3.FName,t3.FModel,t2.FAuxQty,t1.FTranType from RemoteServer.AIS2009.dbo.ICStockBill t1  ";
			sql+="	inner join RemoteServer.AIS2009.dbo.ICStockBillEntry t2 on t1.FInterID = t2.FInterID                        ";
			sql+="	left join RemoteServer.AIS2009.dbo.t_ICItem t3 on t2.FItemID = t3.FItemID                                   ";
			sql+="	where (isnull(t1.FCheckerID,0)>0 and (t1.FCancellation = 0))                                                ";
			sql+="	and (t1.FTranType=24 or t1.FTranType=41)                                                                    ";
			sql+="	and t1.FDate <= @RQDAY2_M                                                                                   ";
			sql+="	and t1.FDate >= @RQDAY1_M                                                                                   ";
			sql+="	and t3.FName like @FName                                                                                    ";
			sql+="	) T group by FBatchNo,FTranType,FModel,FName                                                                ";
		
		  List<Map> list = inventoryMiniDao.queryQTJS(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		TagUtil.datagrid(response, dataGrid);
	}
}
